Comparison a date variable in Jet database sql statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the code below:
------
Dim Mydate as date
Dim MyTableName as String

Dim dbs As Database
Set dbs = CurrentDb
Dim rs1 As DAO.Recordset
...
' caculated and assigned a date to MYdate
' Also assigned a table name to MyTableName
...
Set rs1 = dbs.OpenRecordset _
("SELECT * FROM " & TableName & " WHERE (" & TableName & "!InputDate
= 'Mydate')")
Click to expand...

When executing the code, I got a Type mis-match error for Mydate. When I
change the code to:
("SELECT * FROM " & TableName & " WHERE (" & TableName & "!InputDate
= #11/1/05# )")
Click to expand...
Everything would be fine. Is anyone know the right format for the first sql
statement?

Thanks Much!
 
not sure i follow you. if you get the correct results when you change the
code to the second syntax, then isn't that the "right" syntax? just replace
the hard-coded date with the date variable, as

("SELECT * FROM " & TableName & " WHERE (" & TableName & "!InputDate
= #" & Mydate & "# )")
Click to expand...

since you're only pulling from a single table, you can simplify the SQL
slightly, as

("SELECT * FROM " & TableName & " WHERE InputDate>= #" & Mydate & "#")

but i don't think that will have any impact on how it runs.

hth
 
Hi, Tina:
Thanks for your reply. I was mistakenly think #1/11/05# format is for a
static date. Now I know it also works for a variable date. It works!
Thanks much to your help!
Jim
 
you're welcome :)


Jim in Northwest said:
Hi, Tina:
Thanks for your reply. I was mistakenly think #1/11/05# format is for a
static date. Now I know it also works for a variable date. It works!
Thanks much to your help!
Jim
Click to expand...
 
Jim said:
I have the code below:
Click to expand...


While Tina has got you moving again, I thought you should be
aware of the syntax error in your original query.
The ! should be a .
 
i wondered about that when i saw it, Marshall. i'd never seen the bang used
in a table/field reference in a query before, but since Jim said the query
ran okay with the hard-coded date, i figured i'd better leave well enough
alone... <g>
so i'm glad you commented; now i know that it *isn't* proper syntax. always
learning... :)
 
("SELECT * FROM " & TableName & " WHERE (" & TableName & "!InputDate
Click to expand...

I hate to pick holes, but there are two problems with this.

The code is really not safe for the other 80% of the world that does not
use USAian dates. This method simply asks VBA to use any old conversion
format that has been set in its regional settings.

Another point is that you don't need the tablename in the WHERE clause, and
in fact the ! syntax is incorrect and unrecognised... (I see Marshall has
already picked this up)

This is safe wherever you are:

"SELECT * FROM " & TableName & " " & _
"WHERE InputDate = " & Format(Mydate, "\#mm\/dd\/yyyy\#")


or use the ISO standard format "\#yyyy\-mm\-dd\#"


Best wishes


Tim F
 
Tim Ferguson said:
I hate to pick holes, but there are two problems with this.

The code is really not safe for the other 80% of the world that does not
use USAian dates. This method simply asks VBA to use any old conversion
format that has been set in its regional settings.

Another point is that you don't need the tablename in the WHERE clause, and
in fact the ! syntax is incorrect and unrecognised... (I see Marshall has
already picked this up)

This is safe wherever you are:

"SELECT * FROM " & TableName & " " & _
"WHERE InputDate = " & Format(Mydate, "\#mm\/dd\/yyyy\#")


or use the ISO standard format "\#yyyy\-mm\-dd\#"
Click to expand...

Sorry, let me introduce a third problem.

What happens if the table name has embedded spaces, or other "problem"
characters"

Try

"SELECT * FROM [" & TableName & "] " & _
"WHERE InputDate = " & Format(Mydate, "\#mm\/dd\/yyyy\#")
 
Back
Top