Date and parameter issues with dynamic SQL creation

[DataB]

Hi everyone,

I have this piece of VBA script:

' checking the DOB field to see if we constrain on the date
If Len(Me.EmpDOBTxt & "") > 0 Then
strWhere = strWhere & " AND [Employee].[DOB] <= #" & Me.DOBTxt &
"#"
End If

' check the Banking details fields
If Me.EmpVISAchk = True Then
strWhere = strWhere & " AND [Employee Banking].[VISA]=true"
End If

Basically, I am pulling back input from a form and using it to create
a whereCondition to feed into a report.

Problems:
1. The first piece of code checking DOB works, but only if the date
has a day that is 2 digits, ie. 30/06/2004. If you provide a date like
01/07/2004, it creates the SQL statement truncating the leading '0',
thus 1/07/2004. This then retuns no results when it should return the
same result as the other date entered, ie. 30/06/2004. Don't know why.
Both [Employee].[DOB] and Me.EmpDOBTxt are of Short date type.

2. If the user checks me.EmpVISAchk, when the code runs, a dialog box
is presented asking to provide parameter "[Employee Banking]". Again,
I have no idea why this is happening....... I have tried encasing the
statement like:

(([Employee Banking].[VISA])=true)

and tried many different variations of 'true' - ie. -1, True, (1),
(-1).... I am also pretty sure that have no bound fields on my form,
but am not totally sure - how do you check this?

Any help very much appreciated.

Thanks,

A

[Allen Browne]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"DataB" <ab******@hotmail.com> wrote in message
news:6c**************************@posting.google.c om...

Hi everyone,

I have this piece of VBA script:

' checking the DOB field to see if we constrain on the date
If Len(Me.EmpDOBTxt & "") > 0 Then
strWhere = strWhere & " AND [Employee].[DOB] <= #" & Me.DOBTxt &
"#"
End If

' check the Banking details fields
If Me.EmpVISAchk = True Then
strWhere = strWhere & " AND [Employee Banking].[VISA]=true"
End If

Basically, I am pulling back input from a form and using it to create
a whereCondition to feed into a report.

Problems:
1. The first piece of code checking DOB works, but only if the date
has a day that is 2 digits, ie. 30/06/2004. If you provide a date like
01/07/2004, it creates the SQL statement truncating the leading '0',
thus 1/07/2004. This then retuns no results when it should return the
same result as the other date entered, ie. 30/06/2004. Don't know why.
Both [Employee].[DOB] and Me.EmpDOBTxt are of Short date type.

2. If the user checks me.EmpVISAchk, when the code runs, a dialog box
is presented asking to provide parameter "[Employee Banking]". Again,
I have no idea why this is happening....... I have tried encasing the
statement like:

(([Employee Banking].[VISA])=true)

and tried many different variations of 'true' - ie. -1, True, (1),
(-1).... I am also pretty sure that have no bound fields on my form,
but am not totally sure - how do you check this?

Any help very much appreciated.

Thanks,

A

[Allen Browne]

Issue 1 - Dates
A literal date in the SQL string must be formatted American. Use:

strWhere = strWhere & " AND [Employee].[DOB] <= #" & Format(Me.DOBTxt,
"mm/dd/yyyy") & "#"

For more information on ensuring that Access interprets our dd/mm/yyyy dates
correctly, see:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html
Issue 2 - Parameter
Anything Access does not recognise it assumes is a parameter. This suggests
that there is no table(?) named Employee Banking in the recordset you are
referring to. Perhaps it has different spelling, different spacing, or
perhaps it is not in that query at all.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"DataB" <ab******@hotmail.com> wrote in message
news:6c**************************@posting.google.c om...

Hi everyone,

I have this piece of VBA script:

' checking the DOB field to see if we constrain on the date
If Len(Me.EmpDOBTxt & "") > 0 Then
strWhere = strWhere & " AND [Employee].[DOB] <= #" & Me.DOBTxt &
"#"
End If

' check the Banking details fields
If Me.EmpVISAchk = True Then
strWhere = strWhere & " AND [Employee Banking].[VISA]=true"
End If

Basically, I am pulling back input from a form and using it to create
a whereCondition to feed into a report.

Problems:
1. The first piece of code checking DOB works, but only if the date
has a day that is 2 digits, ie. 30/06/2004. If you provide a date like
01/07/2004, it creates the SQL statement truncating the leading '0',
thus 1/07/2004. This then retuns no results when it should return the
same result as the other date entered, ie. 30/06/2004. Don't know why.
Both [Employee].[DOB] and Me.EmpDOBTxt are of Short date type.

2. If the user checks me.EmpVISAchk, when the code runs, a dialog box
is presented asking to provide parameter "[Employee Banking]". Again,
I have no idea why this is happening....... I have tried encasing the
statement like:

(([Employee Banking].[VISA])=true)

and tried many different variations of 'true' - ie. -1, True, (1),
(-1).... I am also pretty sure that have no bound fields on my form,
but am not totally sure - how do you check this?

[Lyle Fairfield]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:41***********************@per-qv1-newsreader-01.iinet.net.au:

A literal date in the SQL string must be formatted American. Use:

strWhere = strWhere & " AND [Employee].[DOB] <= #" & Format(Me.DOBTxt,
"mm/dd/yyyy") & "#"

If you know of some replicable situation in Access or JET SQL where
yyyy-mm-dd format
results in an error or problem, please, post it here.

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.

[Edward]

ab******@hotmail.com (DataB) wrote in message news:<6c**************************@posting.google. com>...

Hi everyone,

I have this piece of VBA script:

Could you post all of it, please, particularly the rest of the SQL
that the dynamic "where" clause pertains to?

Edward
--
The reading group's reading group:
http://www.bookgroup.org.uk

[Allen Browne]

Hi Lyle

No: I believe that JET will interpret that correctly, but it is not the JET
standard.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lyle Fairfield" <Lo******@FFDBA.Com> wrote in message
news:Xn******************@130.133.1.4...

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:41***********************@per-qv1-newsreader-01.iinet.net.au:

A literal date in the SQL string must be formatted American. Use:

strWhere = strWhere & " AND [Employee].[DOB] <= #" & Format(Me.DOBTxt,
"mm/dd/yyyy") & "#"

If you know of some replicable situation in Access or JET SQL where
yyyy-mm-dd format
results in an error or problem, please, post it here.

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date
--
The e-mail address isn't, but you could use it to find one.