probleme date in where clause

[Borracho1]

Hi i am using following sql statement to filter records of an access db but it doesn't display any records or gives an error when i try it with a variable.
With a normal date everything is working fine.

"AND tblmeeting.Meetingdatum>#" & #8/03/2006# & "#"

i tried also to use the format string around the variable and to convert the string to a date field with (dte = cdate(sdatum)) but also this won't work for me.

This is the SQL statement that i use.

dim sdatum as string
sdatum = date.Now.ToShortDateString 
	
drpsSQL = "SELECT   tblmeeting.MeetingID, tblmeeting.Meetingdatum, tblmeetingaanwezigensub.PersID, tblmeetingaanwezigensub.Meetingaanwezigid" 
drpsSQL = drpsSQL & " " & "FROM tblmeeting INNER JOIN tblmeetingaanwezigensub ON tblmeeting.MeetingID = tblmeetingaanwezigensub.Meetingnr"
drpsSQL = drpsSQL & " " & "WHERE tblmeetingaanwezigensub.PersID =" & stringelogdID & "AND tblmeeting.Meetingdatum>#" & sdatum & "# "

anyone an idea what i am doing wrong here.

thanks a lot

 

[Isadore]

Borracho1: The solution will end up being a simple declaration; make sure you do a thorough serach here, this topic has come up several times (one about 10 days or so ago).

you could try: Dim dte as Date = Now.ToShortDateString()

..and in the SQL: ... WHERE Reqdte = '" & dte & "'...

As one example. Also I find it interesting that when you do get it to work you include a double set of "#" characters whic h would prompt me to remove them from inside the string of the SQL. Do a good search here on formatting Dates in SQL statements and I think you'll run across your solution. Someone else will drop I'm sure to share an idea or two with you. The above is just one approach.

 

[Borracho1]

first thanks for your reply

I aint at home right now so i can't test your suggestion. I apologise if the solution is already on the forum but i wanna say that i searched the forum first and found some similar posts and wanted to reopen one but this one was already archived.

The solution in these other posts was also most of the times the use of the # and the ' or " but i've been trying all these and it just wasn't working. It also wasn't giving any error but it just didn't showed any records.

 

[ca8msm]

The easiest method is to actually write the SQL statement in the MS Access database itself and return the records you want. Once you've done this, you can copy it into your code, assign it to a variable and simply change the date to your variable date. Doing it this way, you'll know that the syntax is correct.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[Borracho1]

ca8msm, i did it exactly like you said, made my query in access and assigned a value to the variable. I saw some other posts and thought that my where clause was right but for some reason my string field could not be compared with a date in an access table. The solution was put in a datareader and always returned an empty grid.

 

[Isadore]

ca8msm's technique should do it for you. The above string I posted earlier works with Access Dates and some variaton of that should suffice. Another string for Dates that works with Access is a follows:

Dim dteF As Date = Request.QueryString("dteF")
Dim dteT As Date = Request.QueryString("dteT")

...WHERE MonthDt Between #" & dteF & "# And #" & dteT & "# ORDER BY MonthDt"

..where the QueryString date is simply:  ...&id=44&dteF=1/1/06&var..

Keep pluggin away, you'll get it.

 

[Borracho1]

I am sorry but this isn't working. Like i said in the first post if i use the following i get exactly one record. I need to put the double # or others it isn't working either. If i remove two # i get different errors.

"AND tblmeeting.Meetingdatum>#" & #8/03/2006# & "#"

Like already mentioned i tried to replace this working example with a variable.

dim dte2 as date
dte2 = Now.ToShortDateString()
>#" & #dte2# & "#"

i get BC30201: Expression expected.

If i remove the inside #, it doesn't gives an error but it isn't showing any records.

the next thing i tried was

"AND tblmeeting.Meetingdatum>'" & dte2 & "'"

another error

because in the last example of isadore he used a string i tried it again as a string

dim adatum as string
	adatum = date.Now.ToShortDateString

"AND tblmeeting.Meetingdatum>#" & #adatum# & "#"
expression expected
without the inside # , no records showed.
"AND tblmeeting.Meetingdatum>'" & adatum & "'"
also an error
"AND tblmeeting.Meetingdatum>'" & #adatum# & "'"
expression expected
"AND tblmeeting.Meetingdatum>#" & adatum & "#"
no records


The i checked again my db, meetingdatum is a date/time field and notation short date. No other restrictions are made.
I also checked my variable with the response.write and it always shows 8/03/2006.
Because i thought the expression expected had something to do with the second & i removed these but then i get the error end of statement expected.

i tried another variant again with a date variable but this one gives an syntaxis error

"AND tblmeeting.Meetingdatum	>#" & "dte2" & "#"

dutch version of the error
'Syntaxisfout in datum. in query-expressie tblmeetingaanwezigensub.PersID =1AND tblmeeting.Meetingdatum >#dte2#. '


So has someone please an idea what i am doing wrong here. I think i already tried every possible combination but it just won't work with variables.

On another forum i read something about using parameters with dates, so i wil first be trying that.

Already thanks for any help or suggestions

 

[Borracho1]

changed it to parameters and now everything is working.

Thanks for all the help

& "AND tblmeeting.Meetingdatum	>@datum4" 

dim dte2 as date
dte2 = Now.ToShortDateString()								
drpoCmd = New OleDbCommand(drpsSQL, drpoConn)
	
dim objParam as OleDbParameter
objParam = drpoCmd.Parameters.Add("@datum4", OleDbType.Date)
objParam.Direction = ParameterDirection.Input objParam.Value = dte2
		
objDataReader = drpoCmd.ExecuteReader(CommandBehavior.CloseConnection)