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.EmpDOBTx t & "") > 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]

Re: Date and parameter issues with dynamic SQL creation



--
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" <abamford@hotma il.com> wrote in message
news:6cd7b21f.0 408162152.4e68f 338@posting.goo gle.com...[color=blue]
> Hi everyone,
>
> I have this piece of VBA script:
>
> ' checking the DOB field to see if we constrain on the date
> If Len(Me.EmpDOBTx t & "") > 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[/color]

[Allen Browne]

Re: Date and parameter issues with dynamic SQL creation

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

strWhere = strWhere & " AND [Employee].[DOB] <= #" & Format(Me.DOBTx t,
"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" <abamford@hotma il.com> wrote in message
news:6cd7b21f.0 408162152.4e68f 338@posting.goo gle.com...[color=blue]
> Hi everyone,
>
> I have this piece of VBA script:
>
> ' checking the DOB field to see if we constrain on the date
> If Len(Me.EmpDOBTx t & "") > 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?[/color]

[Lyle Fairfield]

Re: Date and parameter issues with dynamic SQL creation

"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in
news:4121caa6$0 $16346$5a62ac22 @per-qv1-newsreader-01.iinet.net.au :
[color=blue]
> A literal date in the SQL string must be formatted American. Use:
>
> strWhere = strWhere & " AND [Employee].[DOB] <= #" & Format(Me.DOBTx t,
> "mm/dd/yyyy") & "#"[/color]

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]

Re: Date and parameter issues with dynamic SQL creation

abamford@hotmai l.com (DataB) wrote in message news:<6cd7b21f. 0408162152.4e68 f338@posting.go ogle.com>...[color=blue]
> Hi everyone,
>
> I have this piece of VBA script:[/color]

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]

Re: Date and parameter issues with dynamic SQL creation

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" <LookItUp@FFDBA .Com> wrote in message
news:Xns95483D9 E3A04FFDBA@130. 133.1.4...[color=blue]
> "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in
> news:4121caa6$0 $16346$5a62ac22 @per-qv1-newsreader-01.iinet.net.au :
>[color=green]
>> A literal date in the SQL string must be formatted American. Use:
>>
>> strWhere = strWhere & " AND [Employee].[DOB] <= #" & Format(Me.DOBTx t,
>> "mm/dd/yyyy") & "#"[/color]
>
> 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.[/color]