InStr Function for Criteria of Query 1

[Roe]

I'm having problems creating a InStr Function in a query to print out a report.

The user enters the StartDate (date format) for the Job Number Report but I would like the option of ALL, leaving the user with the choice of entering a date or leaving the prompt box blank.

The InStr Function in the query is:
Field: Expr1:InStry([Start], Blank = All, [FirstEntryT].[Start])

Criteria: >0 OR Is Null

This run but does not list the Startdate of the events in the report even though the StartDate is set to SHOW.

 

[tlbroadbent]

I'm not really sure that I understand the code you posted. Therefore, I could be totally wrong my assumptions.

The SQL code you posted doesn't have StartDate in it so it cannot display on a report. It appears you have a table named FirstEntryT. On the table there is a column named Start. (You don't show StartDate in the query but refer to it in your narrative so I'm unclear on the column name!)

I don't understand the InStr logic. The syntax you posted is incorrect. I can't determine what you want to accomplish with InStr in this query.

You say you want the user to enter a search date or leave the date blank. If that is so then try the following instead of the code you posted.
[tt]
--------------------------------------
Field: [Start]
Table: FirstEntryT

Criteria: [Enter Start Date]
[Enter Start Date] Is Null
--------------------------------------
[/tt]
NOTE:
The code "[Enter Start Date] Is Null" will select ALL records when the user leaves the prompt blank. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Roe]

Thanks tlBroadbent

It work brilliantly.

The StartDate was a narrative the actual field was Start.

I have used the InStr funtion on a previous query for example it was used to produce a report with the option for the user to enter a Client of go for ALL Clients.

Mind you if there is a easier way then I'm all for it.

Thanks again.