Query question further elaboration...

[cornishteeth]

Yesterday I posted this question and hope someone might help further....

Initial question.........
Newbie question. I have a query that contains 3 fields
>from a table. I want to have criteria for each of my
>fields, but I would like to skip a field criteria if I
>desire not to have that info injected into the query. For
>example, I have ShipName, ShipNumber, and Date. Under
>ShipName my criteria is
>
>Like "*"+[Enter Ship Name]+"*".
>
>Under ShipNumber I have [Enter Ship Number]
>
>The query works if all criteria are met. If I do not want
>to enter one of the fields, how do I skip it without it
>affecting the output. If I just hit enter when the Input
>Box appears, the query does not work.
>
>Examples: I enter ALEXANDRIA when Enter Ship Name appears
>and hit the enter key when Enter Ship Number appears.
>Query does not work. I enter ALEXANDRIA when Enter Ship
>Name appears and enter M4567 when Enter Ship Number
>appears. Query works. I hit enter when Ship Name appears
>and enter a Ship Number. Query does not work.


My response.........

Try using a criterion under ShipName of

Like "*"+[Enter Ship Name]+"*" OR [Enter Ship Name] IS NULL

For ShipNumber use

=[Enter Ship Number] OR [Enter Ship Number] IS NULL

Finally, change the name of the Date field - Date is a reserverd word;
you should then be able to use a criterion of

BETWEEN CDate([Start]) AND CDate([Stop])

or, more sophisticated and allowing either Start or Stop to be null,

>= CDate([Start]) OR [Start] IS NULL AND <= CDate([Stop]) OR [Stop] IS NULL


Unfortunately, none of them work. What I am looking for is a way to skip one of the inputs if I maybe do not know the ships name. For example, I entered Seawolf when the ENTER SHIP NAME came up and hit Enter when Ship Number appeared. The query worked; however, when I hit Enter when ENTER SHIP NAME appeared and put in a Ship Number like F3435, it didn't work. Any reasoning why?

Cheers,

Cornish Teeth

 

[mdmilner]

My first instinct is that the easy method is to use VBA to prompt you via InputBox for answers and just handle null responses with code, then build your query.

Other thing I noticed is, I'm so used to using & that I forgot a + character to concatenate that way would even work. Try & instead of +

 

[Parra]

I looked and tried your query criteria on a database I have.

The Enter Ship Name worked fine and so did the enter ship number.

I did'nt have any dates in the database so I was unable to try that. But I think that could be your problem.
Change
BETWEEN CDate([Start]) AND CDate([Stop])
to
Between [Start] and [End]

If you need to change the field name, create a calculated field from the results.