Using form's date field value in sql query

[john]

I'm trying to do something that I thought was simple but has already taken
me hours. I'm trying to use a value from an unbound date field in a query.
Amongst others I tried the following, but I can't get it to work. I tried
several different quotings etc but no luck.

strSQL = "SELECT Transactie.* INTO CatTemp"
strSQL = strSQL & " FROM Transactie"
strSQL = strSQL & " WHERE (([Transactie.Rentedatum]) >=
me.MyFormsDateField);"
DBEngine(0)(0).Execute strSQL, dbFailOnError

Anyone an idea?

Another question. Is there some kind of tool that let's you easily convert
the sql-code from the querybuilder to the visual basic editor? Or is it
normal practice having to divide the query text in separate strings
manually?

Thanks,
john

[RoyVidar]

"john" <jo**@test.comwrote in message
<5Z******************************@casema.nl>:

I'm trying to do something that I thought was simple but has already
taken me hours. I'm trying to use a value from an unbound date field
in a query. Amongst others I tried the following, but I can't get it
to work. I tried several different quotings etc but no luck.

strSQL = "SELECT Transactie.* INTO CatTemp"
strSQL = strSQL & " FROM Transactie"
strSQL = strSQL & " WHERE (([Transactie.Rentedatum]) >=
me.MyFormsDateField);"
DBEngine(0)(0).Execute strSQL, dbFailOnError

Anyone an idea?

Another question. Is there some kind of tool that let's you easily
convert the sql-code from the querybuilder to the visual basic
editor? Or is it normal practice having to divide the query text in
separate strings manually?

Thanks,
john

I think you'll first need to concatenate the value of the form control
into the sql string, in stead of the reference, then Jet needs an
unambiguous date format, wher I prefer the ISO 8601

.... " WHERE Rentedatum >= # & _
format$(me.MyFormsDateField, "yyyy-mm-dd") & "#"

For other acceptable formats, and more information, check out
Allen Brownes article http://allenbrowne.com/ser-36.html

--
Roy-Vidar

[RoyVidar]

"RoyVidar" <ro*************@yahoo.nowrote in message
<mn***********************@yahoo.no>:

"john" <jo**@test.comwrote in message
<5Z******************************@casema.nl>:

>I'm trying to do something that I thought was simple but has already
taken me hours. I'm trying to use a value from an unbound date
field in a query. Amongst others I tried the following, but I can't
get it to work. I tried several different quotings etc but no luck.

strSQL = "SELECT Transactie.* INTO CatTemp"
strSQL = strSQL & " FROM Transactie"
strSQL = strSQL & " WHERE (([Transactie.Rentedatum]) >=
me.MyFormsDateField);"
DBEngine(0)(0).Execute strSQL, dbFailOnError

Anyone an idea?

Another question. Is there some kind of tool that let's you easily
convert the sql-code from the querybuilder to the visual basic
editor? Or is it normal practice having to divide the query text in
separate strings manually?

Thanks,
john

I think you'll first need to concatenate the value of the form
control into the sql string, in stead of the reference, then Jet
needs an unambiguous date format, wher I prefer the ISO 8601

... " WHERE Rentedatum >= # & _
format$(me.MyFormsDateField, "yyyy-mm-dd") & "#"

For other acceptable formats, and more information, check out
Allen Brownes article http://allenbrowne.com/ser-36.html

Forgot (at least one) quote, sorry

.... " WHERE Rentedatum >= #" & _
format$(me.MyFormsDateField, "yyyy-mm-dd") & "#"

--
Roy-Vidar

[volker.loercher]

Hy John,
a few months ago I faced the same problem and this was the solution:
You have to convert your date into SQL-ready date format:

Example:
MyDate = Me.MyDateField
SQLMyDate = "#" & Month(MyDate) & "/" & Day(MyDate) & "/" &
Year(MyDate) & "#"
MsgBox MyDate & vbCrLf & SQLMyDate

after converting SQLMyDate should look like: #26/1/2006#

this variable You can use in SQL statements:

strSQL = "SELECT * FROM MyTable WHERE MyTable.MyDateField = " +
SQLMyDate
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
....
It worked fine for me, hope it does for you

Your last question: I also don't know a tool to get SQL code from the
querybuilder. I'm used to seperate the SQL code into different parts
because it's more flexible and clear when You use it within functions
where You have to deal with variables
strSELECT = "... " ' don't forget the blank at the end of the
strings
strFROM = "... "
strWHERE = "... "
strORDER = "..."

and at the end You add the SQL code together into one string
strSQL = strSELECT + strFROM + strWHERE + strORDER

On 27 Jan., 10:59, "john" <[e-mail]:

I'm trying to do something that I thought was simple but has already taken
me hours. I'm trying to use a value from an unbound date field in a query.
Amongst others I tried the following, but I can't get it to work. I tried
several different quotings etc but no luck.

strSQL = "SELECT Transactie.* INTO CatTemp"
strSQL = strSQL & " FROM Transactie"
strSQL = strSQL & " WHERE (([Transactie.Rentedatum]) >=
me.MyFormsDateField);"
DBEngine(0)(0).Execute strSQL, dbFailOnError

Anyone an idea?

Another question. Is there some kind of tool that let's you easily convert
the sql-code from the querybuilder to the visual basic editor? Or is it
normal practice having to divide the query text in separate strings
manually?

Thanks,
john

[john]

It took me some time but now it works. I used your example and the format
Allen describes, the native SQL notation mm-dd-yyyy. I wonder what has ever
been the logic of this notation???

Thanks a lot Vidar.
john

"RoyVidar" <ro*************@yahoo.noschreef in bericht
news:mn***********************@yahoo.no...

"RoyVidar" <ro*************@yahoo.nowrote in message
<mn***********************@yahoo.no>:

>"john" <jo**@test.comwrote in message
<5Z******************************@casema.nl>:

>>I'm trying to do something that I thought was simple but has already
taken me hours. I'm trying to use a value from an unbound date
field in a query. Amongst others I tried the following, but I can't
get it to work. I tried several different quotings etc but no luck.

strSQL = "SELECT Transactie.* INTO CatTemp"
strSQL = strSQL & " FROM Transactie"
strSQL = strSQL & " WHERE (([Transactie.Rentedatum]) >=
me.MyFormsDateField);"
DBEngine(0)(0).Execute strSQL, dbFailOnError

Anyone an idea?

Another question. Is there some kind of tool that let's you easily
convert the sql-code from the querybuilder to the visual basic
editor? Or is it normal practice having to divide the query text in
separate strings manually?

Thanks,
john

I think you'll first need to concatenate the value of the form
control into the sql string, in stead of the reference, then Jet
needs an unambiguous date format, wher I prefer the ISO 8601

... " WHERE Rentedatum >= # & _
format$(me.MyFormsDateField, "yyyy-mm-dd") & "#"

For other acceptable formats, and more information, check out
Allen Brownes article http://allenbrowne.com/ser-36.html

Forgot (at least one) quote, sorry

... " WHERE Rentedatum >= #" & _
format$(me.MyFormsDateField, "yyyy-mm-dd") & "#"

--
Roy-Vidar

[Lyle Fairfield]

RoyVidar <ro*************@yahoo.nowrote in news:mn.da9b7d71986e0974.59509
@yahoo.no:

I think you'll first need to concatenate the value of the form control
into the sql string, in stead of the reference, then Jet needs an
unambiguous date format, wher I prefer the ISO 8601

... " WHERE Rentedatum >= # & _
format$(me.MyFormsDateField, "yyyy-mm-dd") & "#"

A simple hack is

.... " WHERE Rentedatum >=" & CDbl(MyFormsDateField.Value)

[RoyVidar]

"Lyle Fairfield" <[e-mail] in message
<Xn******************@216.221.81.119>:

RoyVidar <ro*************@yahoo.nowrote in
news:mn.da9b7d71986e0974.59509 @yahoo.no:

>I think you'll first need to concatenate the value of the form
control into the sql string, in stead of the reference, then Jet
needs an unambiguous date format, wher I prefer the ISO 8601

... " WHERE Rentedatum >= # & _
format$(me.MyFormsDateField, "yyyy-mm-dd") & "#"

A simple hack is

... " WHERE Rentedatum >=" & CDbl(MyFormsDateField.Value)

Ah, yes - that's OK for *dates*, but when there's a time fraction ....

....say where I live, then I'd get 3075 Syntax error (comma) in query
expression...

.... because we use comma as decimalseparator, which makes Jet barf on
39109,6325462963

Replace(CDbl(MyFormsDateField.Value), ",", ".") would work, as will
converting to integer ;-)

--
Roy-Vidar