Access Database using do.cmd openform where clasue - returning all values
- by primus285
DoCmd.OpenForm "Database Search", acFormDS, , srcLastName & "AND " & srcFirstName
This is only a small sample of the where clause - there are many more terms.
First, there is a set of If, Then type tings up top that set the variable srcLastName and srcFirstName to some value. These are not the problem and work just fine.
The trouble is getting them to return all values (for instance if you only want to search by one, on neither(return full database list))
Thus far I have settled for (in the if then section):
srcLastName = "[Lastname] =" & Chr(34) & cboLastName & Chr(34) - to search for something and
srcLastName = "[Lastname] <" & Chr(34) & "Nuthin" & Chr(34) - to return everything (not equal to an absurd and mispelled database term.)
The trouble is that data that is null is also not returned. If I have a null firstname, it will not show up in any search period.
is there a term I can set [lastname] and [firstname] equal to that will return EVERYTHING (null, open, data, numbers, wierd stuff and otherwise) in a search
an SQL form of "give me everything shes got scotty" if you will.
the real issue here comes from the data entry - if I could just know that the people would enter everything 100% of the time, this code would work. but forget to enter the persons age or whatever, and it wont return that entry.
So far, the only other solution I have come up with is to put a counter in each if then statement. The count will go up by one for each thing that is being searched by. Then if the count is = 1, then I can search by something like just
DoCmd.OpenForm "Database Search", acFormDS, , srcLastName
or
DoCmd.OpenForm "Database Search", acFormDS, , srcFirstName
then revert back to the
DoCmd.OpenForm "Database Search", acFormDS, , srcLastName & "AND " & srcFirstName
when the count is 2 or more
truoble here is that it only works for one (unless I so wanted to create a custon list of 2 combined, 3 combined, 4 combined, but that is not happening)
