Difference between 6 and 06

[The Bey]

I've a query where the parameters are entered by the user. I want the user to be able to return all values if the parameters are left blank and I can reach this with

'Like "*" & [...] & "*"'

But the problem that I'm having is that the field of interest can have values like "1" and "01", so if the user enters "1" then they will return all records which contain "1" in that field, thus returning the incorrect values.

Is there a way that I can set this in VBA or in its properties to specify that there is a difference?

 

[John Big Booty]

If you are using;

Like "*" & [...] & "*"

As you criteria, you will always return all records that contain "1"
You could try something like;

Like IIf(IsNull([Forms]![FormName]![FieldName]),"*",[Forms]![FormName]![FieldName])

 

[The Bey]

Excellent! It seems to work great!

I can't fully test it at the moment as this is more of a pre-emptive addition as my raw test data only contains "06", but eventually it will contain values such as 1-01, 4-04, 6-06 and I want to try to get this covered so it can be entered without a hitch.

 

[John Big Booty]

That code should do all that for you.