Open form with criteria on two fields

[Newbee]

Hi,

How do I open form with criteria on two fields?
I have form with 2 combo boxes, and need to open another form with criteria
based on this 2 combo
boxes and to show record which contains values from this combo boxes.
First form has fields Cbo1 and Cbo2. Cbo1 is date field.
On form which is opening there are 2 fields (they both consist primary key
of table): [Date] and [Field2].

Thanks for helping.

 

[Van T. Dinh]

DoCmd.OpenForm "SecondFormName", , , _
"[DateField] = [Cbo1] And [Field2] = [Cbo2]"

HTH
Van T. Dinh
MVP (Access)

 

[John Vinson]

Hi,

How do I open form with criteria on two fields?
I have form with 2 combo boxes, and need to open another form with criteria
based on this 2 combo
boxes and to show record which contains values from this combo boxes.
First form has fields Cbo1 and Cbo2. Cbo1 is date field.
On form which is opening there are 2 fields (they both consist primary key
of table): [Date] and [Field2].

Thanks for helping.

The WhereCondition argument of the OpenForm condition can be an almost
arbitrarily complicated expression: it's just a SQL query WHERE clause
without the word WHERE. Your code could be something like

DoCmd.OpenForm strDocument,WhereCondition:="[Date] = #" & Me!cbo1 & _
"# AND [Field2] = " & Me!cbo2

If Field2 is text, you need quotemarks:

[Field2] = '" & Me!cbo2 & "'"

 

[Newbee]

Thanks John and Van, but is not working. I got error message:
Run time error '3075'
Syntax error in number in query expression '[Field1]=4 AND
[Date]=05.02.2004'.

Do you know where is mistake?

Thanks

 

[Newbee]

I think I know where is mistake but don't know how to solve problem.
In cbo1 is for Row source this statement:
SELECT tblData.NameID, tblData.Name FROM tblData ORDER BY [Name];

When I choose Name in combo box (cbo1), and date in another combo box
(cbo2), I should base criteria on NameID and Date. But user in combo box 1
chooses Name, not NameID of that Name.
Do you have any solution?

Thanks

 

[Douglas J. Steele]

John pointed out that you need # delimiters around dates, but he forgot to
mention that you also must have the date in mm/dd/yyyy format, regardless of
what your short date format is set to.

In other words, your WHERE clause needs to be
....[Field1]=4 AND [Date]=#02/05/2004#.

Note that you can use the Format function to convert your date to the
correct format: it's not necessary to force your users to use an unnatural
format.

Another option is
....[Field1]=4 AND [Date]= CDate('05.02.2004')

since CDate respects your regional settings. Note that you must have the
quotes inside the CDate function call though.

 

[John Vinson]

I think I know where is mistake but don't know how to solve problem.
In cbo1 is for Row source this statement:
SELECT tblData.NameID, tblData.Name FROM tblData ORDER BY [Name];

When I choose Name in combo box (cbo1), and date in another combo box
(cbo2), I should base criteria on NameID and Date. But user in combo box 1
chooses Name, not NameID of that Name.
Do you have any solution?

The Combo Box can be based on a Query with two fields: the NameID (as
the bound column, with width of zero in the ColumnWidths property),
and the Name (the second column, nozero width). The user sees the
name, the computer sees the ID, and they're both happy.

If you have dates in cbo2's RowSource query you will need to use #
date delimiters and American date format: Access was written in
Washington State and the programmers decided on American mm/dd/yy as
the date standard. I'd suggest a WhereCondition such as

WhereCondition:="[Date] = #" & Format(CDate(Me!cbo2,"mm/dd/yyyy")) & _
"# AND [NameID] = " & Me!cbo1