VBA and SQL re:Calender variable

[AndyB]

I have a VBA code like this
' "Calender" is my ActiveX calender

Dim strqry As String
Dim eDate As Date
Dim sdate As Date
eDate = ([Calender] - 27)
sdate = [Calender]
This just checks the variables are set
MsgBox sdate
MsgBox eDate

' DoCmd.OpenQuery qryname
' try putting in SQL direct
strqry = "SELECT [Mytable].* INTO [NewTable]"
strqry = strqry + "FROM [Mytable]"

strqry = strqry + "WHERE ((([Mytable].[Date_Report])>=[sdate] And
([Mytable].[Date_Report])<=[edate]));"

DoCmd.RunSQL strqry


It does not work I get the correct dates from the message box but they are
not carried over to the query.
I even set the DIm to string but it does not work.
What am I missing

Andy

 

[Dan Artuso]

Hi,
You can't have the variables as part of the string, they become literal values.
I'm sure that none of the values in your Date_Report field are = sdate
They might be equal to the value of sdate however.
You have to let Access evaluate the value of your variables and then concatenate them
into your string (dates have to be delimited with #)

strqry = strqry + "WHERE ((([Mytable].[Date_Report])>=#" & [sdate] & _
"# And ([Mytable].[Date_Report])<=#" & [edate] & "#));"

Always issue a debug.Print statement before executing so you can see what your
sql string looks like.

 

[AndyB]

I gave it a try and I still get an error that states

This object does not support this property or method.



Andy

 

[Douglas J. Steele]

Try printing out the SQL string you're using

strqry = "SELECT [Mytable].* INTO [NewTable]"
strqry = strqry + "FROM [Mytable]"
strqry = strqry + "WHERE ((([Mytable].[Date_Report])>=#" & [sdate] & _
"# And ([Mytable].[Date_Report])<=#" & [edate] &
"#));"

Debug.Print strqry

DoCmd.RunSQL strqry

You'll find it's got some problems in it: you don't have any blanks between
[NewTable]FROM [Mytable]WHERE

And while I hate to argue with Dan, if your short date format happens to be
set to dd/mm/yyyy, then that code is going to fail anytime the day value for
sdate or edate is 1 through 12.

Try changing to:

strqry = "SELECT [Mytable].* INTO [NewTable] "
strqry = strqry & "FROM [Mytable] "
strqry = strqry & "WHERE ((([Mytable].[Date_Report])>=" & Format([sdate],
"\#mm\/dd\/yyyy\#") & _
" And ([Mytable].[Date_Report])<=" &
Format([edate], "\#mm\/dd\/yyyy\#")

just to be sure.

 

[Dan Artuso]

Hey Doug,
No need to argue, I agree with you

--
HTH
Dan Artuso, Access MVP

Try printing out the SQL string you're using

strqry = "SELECT [Mytable].* INTO [NewTable]"
strqry = strqry + "FROM [Mytable]"
strqry = strqry + "WHERE ((([Mytable].[Date_Report])>=#" & [sdate] & _
"# And ([Mytable].[Date_Report])<=#" & [edate] &
"#));"

Debug.Print strqry

DoCmd.RunSQL strqry

You'll find it's got some problems in it: you don't have any blanks between
[NewTable]FROM [Mytable]WHERE

And while I hate to argue with Dan, if your short date format happens to be
set to dd/mm/yyyy, then that code is going to fail anytime the day value for
sdate or edate is 1 through 12.

Try changing to:

strqry = "SELECT [Mytable].* INTO [NewTable] "
strqry = strqry & "FROM [Mytable] "
strqry = strqry & "WHERE ((([Mytable].[Date_Report])>=" & Format([sdate],
"\#mm\/dd\/yyyy\#") & _
" And ([Mytable].[Date_Report])<=" &
Format([edate], "\#mm\/dd\/yyyy\#")

just to be sure.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I gave it a try and I still get an error that states

This object does not support this property or method.



Andy

 

[AndyB]

Thanks all

It was tough going at first. I find that with all my programming


It works, I had to add two ))

Format([edate], "\#mm\/dd\/yyyy\#")


But it works.


Andy B

 

[AndyB]

and just one more thing. More of a discussion then anything else. I
wanted to use that SQL in VBA because it was the way to go since I was using
a calender control in a form to access a range of dates. In some of my other
procedures I use VBA just to open the query up from the Queries I have
already written. Is there a general benefit one way or the other?


Andy B