Help with OpenForm Where Condition

[Gary Dolliver]

Hi all,
I am using a DoCmd.OpenForm command from form1, and attempting to have form2
open with the criteria entered on form1. On form1, I have a text field,
titled LastName, where the entry goes. When a button is pushed on form1, I
want form2 to open and the where condition in the OpenForm to pass the field
from form1 into the where condition. I am using the following:
DoCmd.OpenForm "INQUIRY_MAIN_View", , , "ShipTo_LName=" & LastName.Value
What is happening is when I click the button, it brings up an entry box with
the value I entered into the LastName field as the title. Instead, I would
just like the form to open. I have tried other formats, setting variables,
but have not had any luck. Help please! Thanks!
-gary

 

[Damian S]

Hi Gary,

Try:

DoCmd.OpenForm "INQUIRY_MAIN_View", , , "ShipTo_LName= '" & me.LastName & "'"

that's a single quote after the equals sign, and a single quote inside
double quotes

Damian.

 

[Gary Dolliver]

Damian, you rock! Thank you so much!

Hi Gary,

Try:

DoCmd.OpenForm "INQUIRY_MAIN_View", , , "ShipTo_LName= '" & me.LastName & "'"

that's a single quote after the equals sign, and a single quote inside
double quotes

Damian.

 

[Gary Dolliver]

Hi again,
Is it possible to have multiple where conditions in this statement? Would I
just use an AND or an OR?
For example, if I wanted to look up a last name within a zip code?
DoCmd.OpenForm "INQUIRY_MAIN_View", , , "ShipTo_LName= '" & me.LastName &
"'" AND "ShipTo_ZIP5= "' & me.ZipCode & "'"
(I tried the above and I got a type mismatch error)
Thanks again!
-gary

 

[Damian S]

Hi again Gary,

Yes, it is possible, use AND if you want BOTH conditions to be true, use OR
if you want one or the other or both to be true.

You get a type mismatch if you are trying to match different data types, eg:
is your zip code field a number? If so, you don't need to surround it in
single quotes.

"' AND [ShipTo_ZIP5] = " & me.ZipCode

Damian.

 

[Gary Dolliver]

Hi again,
Man, to make matters more fun, my ZIP field is a text field as I need to
also capture Canadian Zip codes as they have letters in them. I have tried
different combinations, but cannot get it to work - still getting the Type
error.
If I only use one field or the other (LName or ZIP5) it works great
(treating them both as text fields) - When I used your example, it also
mentioned a type error - any ideas? I am stumped...
thanks!
-gary

Hi again Gary,

Yes, it is possible, use AND if you want BOTH conditions to be true, use OR
if you want one or the other or both to be true.

You get a type mismatch if you are trying to match different data types, eg:
is your zip code field a number? If so, you don't need to surround it in
single quotes.

"' AND [ShipTo_ZIP5] = " & me.ZipCode

Damian.

Hi again,
Is it possible to have multiple where conditions in this statement? Would I
just use an AND or an OR?
For example, if I wanted to look up a last name within a zip code?
DoCmd.OpenForm "INQUIRY_MAIN_View", , , "ShipTo_LName= '" & me.LastName &
"'" AND "ShipTo_ZIP5= "' & me.ZipCode & "'"
(I tried the above and I got a type mismatch error)
Thanks again!
-gary

 

[Damian S]

Hi Gary,

You have probably worked it out by now, but here it is anyway...

DoCmd.OpenForm "INQUIRY_MAIN_View", , , "ShipTo_LName= '" & me.LastName & "'
AND [ShipTo_ZIP5] = '" & me.ZipCode & "'"

You had too many double quotes around ShipTo_ZIP5.

Damian.

Hi again,
Man, to make matters more fun, my ZIP field is a text field as I need to
also capture Canadian Zip codes as they have letters in them. I have tried
different combinations, but cannot get it to work - still getting the Type
error.
If I only use one field or the other (LName or ZIP5) it works great
(treating them both as text fields) - When I used your example, it also
mentioned a type error - any ideas? I am stumped...
thanks!
-gary

Hi again Gary,

Yes, it is possible, use AND if you want BOTH conditions to be true, use OR
if you want one or the other or both to be true.

You get a type mismatch if you are trying to match different data types, eg:
is your zip code field a number? If so, you don't need to surround it in
single quotes.

"' AND [ShipTo_ZIP5] = " & me.ZipCode

Damian.

Hi again,
Is it possible to have multiple where conditions in this statement? Would I
just use an AND or an OR?
For example, if I wanted to look up a last name within a zip code?
DoCmd.OpenForm "INQUIRY_MAIN_View", , , "ShipTo_LName= '" & me.LastName &
"'" AND "ShipTo_ZIP5= "' & me.ZipCode & "'"
(I tried the above and I got a type mismatch error)
Thanks again!
-gary

:

Hi Gary,

Try:

DoCmd.OpenForm "INQUIRY_MAIN_View", , , "ShipTo_LName= '" & me.LastName & "'"

that's a single quote after the equals sign, and a single quote inside
double quotes

Damian.

:

Hi all,
I am using a DoCmd.OpenForm command from form1, and attempting to have form2
open with the criteria entered on form1. On form1, I have a text field,
titled LastName, where the entry goes. When a button is pushed on form1, I
want form2 to open and the where condition in the OpenForm to pass the field
from form1 into the where condition. I am using the following:
DoCmd.OpenForm "INQUIRY_MAIN_View", , , "ShipTo_LName=" & LastName.Value
What is happening is when I click the button, it brings up an entry box with
the value I entered into the LastName field as the title. Instead, I would
just like the form to open. I have tried other formats, setting variables,
but have not had any luck. Help please! Thanks!
-gary

 

[Gary Dolliver]

Thank you! I did get it with some more help - but I do appreciate the
response and solution!
-gary

Hi Gary,

You have probably worked it out by now, but here it is anyway...

DoCmd.OpenForm "INQUIRY_MAIN_View", , , "ShipTo_LName= '" & me.LastName & "'
AND [ShipTo_ZIP5] = '" & me.ZipCode & "'"

You had too many double quotes around ShipTo_ZIP5.

Damian.

Hi again,
Man, to make matters more fun, my ZIP field is a text field as I need to
also capture Canadian Zip codes as they have letters in them. I have tried
different combinations, but cannot get it to work - still getting the Type
error.
If I only use one field or the other (LName or ZIP5) it works great
(treating them both as text fields) - When I used your example, it also
mentioned a type error - any ideas? I am stumped...
thanks!
-gary

Hi again Gary,

Yes, it is possible, use AND if you want BOTH conditions to be true, use OR
if you want one or the other or both to be true.

You get a type mismatch if you are trying to match different data types, eg:
is your zip code field a number? If so, you don't need to surround it in
single quotes.

"' AND [ShipTo_ZIP5] = " & me.ZipCode

Damian.

:

Hi again,
Is it possible to have multiple where conditions in this statement? Would I
just use an AND or an OR?
For example, if I wanted to look up a last name within a zip code?
DoCmd.OpenForm "INQUIRY_MAIN_View", , , "ShipTo_LName= '" & me.LastName &
"'" AND "ShipTo_ZIP5= "' & me.ZipCode & "'"
(I tried the above and I got a type mismatch error)
Thanks again!
-gary

:

Hi Gary,

Try:

DoCmd.OpenForm "INQUIRY_MAIN_View", , , "ShipTo_LName= '" & me.LastName & "'"

that's a single quote after the equals sign, and a single quote inside
double quotes

Damian.

:

Hi all,
I am using a DoCmd.OpenForm command from form1, and attempting to have form2
open with the criteria entered on form1. On form1, I have a text field,
titled LastName, where the entry goes. When a button is pushed on form1, I
want form2 to open and the where condition in the OpenForm to pass the field
from form1 into the where condition. I am using the following:
DoCmd.OpenForm "INQUIRY_MAIN_View", , , "ShipTo_LName=" & LastName.Value
What is happening is when I click the button, it brings up an entry box with
the value I entered into the LastName field as the title. Instead, I would
just like the form to open. I have tried other formats, setting variables,
but have not had any luck. Help please! Thanks!
-gary