Docmd.Openform with where condition Syntax

Hello, im trying to open a form from an dialog box form:

the button on the dialog box has this on the onclick event:


DoCmd.OpenForm "frmCASES_UNION", acViewNormal, , "MCH_CASECODE = #" &
Me!txtCCODE & "#"

the form I'm trying to open is "frmcases_union"
and im trying to filter it by the "MCH_CASECODE" which i type into
txtCCODE.

When i click i get:
Run-time error 3705
Syntax error in date in query expression MCH_CASECODE = #1360#

is it because im using # signs which makes it think its a date?? what
should i use as placeholders?

Thanks, Gavin

Re: Docmd.Openform with where condition Syntax

On Wed, 14 Nov 2007 22:09:46 -0000, gavm360@yahoo.com wrote:

> Hello, im trying to open a form from an dialog box form:
>
> the button on the dialog box has this on the onclick event:
>
> DoCmd.OpenForm "frmCASES_UNION", acViewNormal, , "MCH_CASECODE = #" &
> Me!txtCCODE & "#"
>
> the form I'm trying to open is "frmcases_union"
> and im trying to filter it by the "MCH_CASECODE" which i type into
> txtCCODE.
>
> When i click i get:
> Run-time error 3705
> Syntax error in date in query expression MCH_CASECODE = #1360#
>
> is it because im using # signs which makes it think its a date?? what
> should i use as placeholders?
>
> Thanks, Gavin

Why are you using
"MCH_CASECODE = #" Me!txtCCODE & "#" ?
The # is a date delimiter symbol. By your surrounding your variable
with #, Access is expecting a date value, but MCH_CASECODE is not a
date field.

What is the datatype of the "MCH_CASECODE" field?

If MCH_CASECODE is Text datatype, then use:
"MCH_CASECODE ='" & Me!txtCCODE & "'"

The above will return the 1360 value as text:
"MCH_CASECODE = '1360'"

If it is a Number datatype then use:
"MCH_CASECODE =" & Me!txtCCODE

The above will return the 1360 value as a Number
"MCH_CASECODE = 1360"
I would suspect that you need the Number datatype syntax.



--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Re: Docmd.Openform with where condition Syntax

Yes, the # is used as delimiters around literal dates in the string.

If MCH_CASECODE is a Text field (when you open the field in design view),
use quote marks. Example:
Dim strWhere As String
If Not IsNull(Me.txtCCCODE) Then
strWhere = "MCH_CASECODE = """ & Me!txtCCODE & """"
End If
DoCmd.OpenForm "frmCASES_UNION", acViewNormal, , strWhere

By using a variable for the WhereCondition, you can add the line:
Debug.Print strWhere
and see what it prints to the Debug window if it fails.

For an explanation of the extra quotes, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

--
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.

wrote in message
news:1195078186.540158.115500@22g2000hsm.googlegroups.com...
> Hello, im trying to open a form from an dialog box form:
>
> the button on the dialog box has this on the onclick event:
>
>
> DoCmd.OpenForm "frmCASES_UNION", acViewNormal, , "MCH_CASECODE = #" &
> Me!txtCCODE & "#"
>
> the form I'm trying to open is "frmcases_union"
> and im trying to filter it by the "MCH_CASECODE" which i type into
> txtCCODE.
>
> When i click i get:
> Run-time error 3705
> Syntax error in date in query expression MCH_CASECODE = #1360#
>
> is it because im using # signs which makes it think its a date?? what
> should i use as placeholders?
>
> Thanks, Gavin
>

Re: Docmd.Openform with where condition Syntax

Thanks guys, that worked. I was incorrectly using the # placeholder
for a number datatype