open form criteria, text not numeric

  • Thread starter Thread starter r
  • Start date Start date
R

r

A step in my macro is to open a form where the record in the new form will
match the value selected on the first form's dropdown box. The IDs,
unfortunately, are text and not numeric, so I'm having trouble finding the
right record in the set.

my "where" is:

[customerID] = [forms]![details]![customerID]

but despite my variations in writing this, I get either a type mismatch, or
a new record comes up (though I can see the filter has been applied).

The dropdown box I'm using to select the customerID on the first form does
not
have a control source, though I'm certain it's bound to the correct value
(the ID)
and not the customer name, which is the only other field displayed.

I'm guessing this is just a problem because the equation thinks i'm looking
for a numeric value, but I'm not sure how to put quotes, or whatever, to
indicated the value is text.

Help?!
 
Try this for string
"[customerID] = '" & [forms]![details]![customerID] & "'"

For Number
"[customerID] = " & [forms]![details]![customerID]

For Date
"[DateField] = #" & [forms]![details]![DateField] & "#"
 
Thanks. Unfortunately, the one for string did not work. The "where"
statement is part of the OpenForm "Where" criteria in a macro, if that
helps.


Ofer said:
Try this for string
"[customerID] = '" & [forms]![details]![customerID] & "'"

For Number
"[customerID] = " & [forms]![details]![customerID]

For Date
"[DateField] = #" & [forms]![details]![DateField] & "#"

r said:
A step in my macro is to open a form where the record in the new form will
match the value selected on the first form's dropdown box. The IDs,
unfortunately, are text and not numeric, so I'm having trouble finding the
right record in the set.

my "where" is:

[customerID] = [forms]![details]![customerID]

but despite my variations in writing this, I get either a type mismatch, or
a new record comes up (though I can see the filter has been applied).

The dropdown box I'm using to select the customerID on the first form does
not
have a control source, though I'm certain it's bound to the correct value
(the ID)
and not the customer name, which is the only other field displayed.

I'm guessing this is just a problem because the equation thinks i'm looking
for a numeric value, but I'm not sure how to put quotes, or whatever, to
indicated the value is text.

Help?!
Click to expand...
Click to expand...
 
A step in my macro is to open a form where the record in the new form will
match the value selected on the first form's dropdown box. The IDs,
unfortunately, are text and not numeric, so I'm having trouble finding the
right record in the set.

my "where" is:

[customerID] = [forms]![details]![customerID]
Click to expand...

Try putting in the syntactically required quotemarks:

Dim strWhere As String
strWhere = "[CustomerID] = '" & Forms!Details!CustomerID & "'"
DoCmd.OpenForm strWhere

John W. Vinson[MVP]
 
Back
Top