0

I am new to Visual Basic as well as Stackoverflow. I am querying certain fields from a database but receiving an error. here's my code:

Dim fifdate As Date = Now()
fifdate.AddDays(-15)

db.AddParam("collected", "N")
db.AddParam("printed", "Y")
db.AddParam("sent", "Y")
db.AddParam("date", fifdate)




Dim query As String = "Select * from badcheck where fldcollected = 
@collected And fldprinted = @printed And fldsentda = @sent "

'And fldsentdate > @date

db.ExcecuteQuery(query)

CheckedListBox1.DataSource = db.DBDT

if i add the commented out section into the query, i get the error "Data type mismatch in criteria expression", but the query works perfectly fine without it. I know for sure that fldsentdate is set as a date in the database. any suggestion on how the issue can be fixed?

Ňɏssa Pøngjǣrdenlarp
  • 38,411
  • 12
  • 59
  • 178
bigboi214
  • 21
  • 4
  • `db.AddParam("@collected", "N")`, yada-yada-yada. Don't make your boolean columns Y-N strings. – LarsTech Jun 07 '17 at 17:31
  • 1
    Thank you for your insight. I'm working with a database thats already created. For some reason, the creator set those fields as characters and not boolean. – bigboi214 Jun 07 '17 at 17:35
  • What happens when you change the commented portion to read, `And fldsentdate > #@date#` Microsoft Access prefers # to enclose date values – Jimmy Smith Jun 07 '17 at 18:24
  • Gives syntax error. Bout to do more research. – bigboi214 Jun 07 '17 at 18:27
  • Odd, is fldSentDate actually a date field within the database? Try the query direct with # and it should work. With oledb, I had to use ? where you're using named parameters. This may have been because I use an older version of Access. – Jimmy Smith Jun 07 '17 at 18:30
  • Not like the # at all. Is it possible to use fifdate directly without adding it as a parameter? – bigboi214 Jun 07 '17 at 18:35
  • 2
    @JimmySmith You don't use the # sign when using parameters. The engine will take care of that for you. – LarsTech Jun 07 '17 at 18:37
  • 2
    The problem is likely in that `db` thing (probably a SQL "helper"?) and/or the AddParam method. Also, do not add tags to the title, thats what tags are for, read [ask] and take the [tour] since you are new. – Ňɏssa Pøngjǣrdenlarp Jun 07 '17 at 18:46

1 Answers1

2

i was able to fix the problem. I took the fifdate variable completely out and changed my parameter code to:

db.AddParam("date". date.today.AddDays(-15))

something was going wrong with fifdate, and I believe it may be due to Now(). i tried using fifdate directly with the query and was receiving the same error.

bigboi214
  • 21
  • 4
  • A-ha, it was something completely different. I've had issues with DateAdd and using Now. I believe the issue was the time piece of the date is more accurate to milli/nano seconds and changes the format of the date (versus what Access is accustomed to) – Jimmy Smith Jun 07 '17 at 18:56
  • 1
    I was thinking the same thing. Instead of it giving the variable a date only, it was giving it the date as well as the time and my database variable was only a date. – bigboi214 Jun 07 '17 at 18:58
  • the problem is that DateTime vars are immutable; the math operations do not change the variable but return a **new** variable with the change. the correct code would be `fifdate = fifdate.AddDays(-15)` or simply `fifdate = DateTime.Now.AddDays(-15)`. You probably want to strip the time off too using `.Date` – Ňɏssa Pøngjǣrdenlarp Jun 07 '17 at 19:04