Date query

[esusda]

I've looked through all of the Date posts I can find, but still haven't worked out this relatively simple problem, so here goes:

I'm currently just trying to do a simple SELECT retrieval using a date entered into a text box (format set to General Date with input mask 00/00/0000

. This information is then passed into the SELECT statement as follows:

strSQL = "SELECT DISTINCTROW PROJECT_ID FROM GT_ACTUALS WHERE CAL_DATE = " & txtStartDate.value & ";"

This returns 'No Current Record' despite there being ones in there. It's obviously to do with the whole date format side, and the way Access stores dates, since if I amend the above to:

strSQL = "SELECT DISTINCTROW PROJECT_ID FROM GT_ACTUALS WHERE CAL_DATE = 01/06/2001;"

..it still doesn't work.

I'd very much appreciate a pointer on how to get this working - it's frustratingly simple no doubt!

Thanks
~esusda~

 

[Drew]

I think you're right! it's very frustratingly simple, dates need to be identified as such when they go into a query. One of those things that bugs for hours until you know it, then you never think about it again. If you surround the date with #'s it should work eg

strSQL = "SELECT DISTINCTROW PROJECT_ID FROM GT_ACTUALS WHERE CAL_DATE = #01/06/2001#;"

Also, beware the American date format, if you're from a more logical part of the world (

) you'll need to use
strSQL = "SELECT DISTINCTROW PROJECT_ID FROM GT_ACTUALS WHERE CAL_DATE = #" & format(txtStartDate.value,"MM/DD/YYYY") & "#;". In case you come up against similar on other queries, strings/text need to be surrounded by "" or '', booleans and numbers you can do without anything.

Finally, just for the record, if for some reason you don't want to use #s you can convert the date to its base number and query on that eg.
strSQL = "SELECT DISTINCTROW PROJECT_ID FROM GT_ACTUALS WHERE CAL_DATE = " & CDbl(me.StartDate.value) & ";".

HTH

Drew

[This message has been edited by Drew (edited 09-07-2001).]

 

[esusda]

Thanks Drew, got it working!

I tried surrounding the date with #'s long ago without any success, so initially wasn't reading your reply with much hope. Since I'm in the UK, I hadn't even thought about American date format as being the problem!

In fact I just tried #" & format(txtStartDate.value,"MM/DD/YYYY") & "# and it's worked - the only thing I can think of is that the stupid database I'm accessing stores in one format but is formatted to display in the other..

Anyway thank you so much!
~esusda~

 

[Drew]

yep, that stupid db called access pretends to work in a uk format but look at the SQL of a query and it's translated into american. As a fellow englander, but from little-london-on-sea, i wholly sympathise any frustration you feel at the gross stupidity/laziness of M$ not fully coding date conversions, t*sse*s

Caught me out for quite a while too.

glad the answer was in there, even if it wasn't the one i thought it was

Drew

 

[Drew]

sorry Pat, i don't mean to criticise but ANSI dates ( noting the American tone of the std ) are YYYY-MM-DD or YYYYMMDD, not mm/dd/yy. The point was that m$ fakes working in one while really working in the other, if it worked exclusively in one or other the confusion wouldn't exist. The format of the client machine shouldn't matter as it's all turned into numbers anyhow - today is 37141 on my pc, what is it on yours?

please don't take this offensively at all i appreciate the skills you have and the effort you put into this forum

Drew