Is there a way to make a query treat the field as a literal instead of value?
Not sure you're expressing yourself correctly here. A literal would be WHERE Pet = "Dog". In query design view, this would be the word "Dog" in the Pet field. Dog (the criteria) is the literal.
How can I get a query to treat a form field as if it was typed directly into the query?
Suggest you always post what you tried. "I tried 'Like' " doesn't mean your syntax was correct. Note: LIKE can only be used with text, not numerical values.
There are several ways to take care of this. I suspect you're more interested in the fastest fix. If we're talking text values in the control, try
LIKE "*" & Forms!YourFormName.YourControlName & "*" . If the control contains nothing, all records should be returned.
The alternatives are:
- validating that controls needed for a query are not empty (null) or don't contain numbers where text is required (or vice versa)
- building sql in vba. The main part (constant) is assigned to a string variable (strFirstPart, or whatever). The WHERE part is assigned to strWhere, but the code for this tests controls for values and if not found, excludes them from the WHERE string. Any other volatile parts are assigned to other string variables, such as ORDER BY in strOrder. Somewhere along the line, the whole thing gets strung together.
"0 or -1" again, it's treating it as text instead of a command where you'd normally put it in a query.
If there are issues where your query treats a number as text or vice versa, then it is a problem of using quotes where you should not, or not where you should. Any value wrapped in " " or ' ' is treated as text. It's one of the more confounding tasks for newcomers to properly concatenate sql parts when using control references instead of values when creating sql in code. Not sure if you're doing that or not. Regardless, as previously noted, these values don't make for good data types where Nulls are allowed. Add to that the oddity that while 0 is always False in Access, sometimes True is 1, sometimes it's -1. I think they're best avoided unless the field cannot except null.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.