Wildcard queries 1

[FireViking]

I have created a query with multiple feilds and assigned wildcard criteria linked to my form to each feild. The following is an eg.
LIKE "*" & [forms]![formname]![formfeild] & "*"
this works fine when the query is run.
I have created a form with the intention of the user entering data into any 1 or more of the feilds to perform a search using the query and the results being shown in a listbox. The process works fine until I try to add a search between two dates using:
Between [Forms]![formname]![formfeild] And [Forms]![formname]![formfeild2]
This syntax works fine if no other criteria exist.
I thought this would work fine along with other feild criterias but it does not. The query returns nothing.
Is this the right syntax?

 

[KenReay]

Hi

In you first example you are clearly building an SQL string in code eg:

LIKE "*" & [forms]![formname]![formfeild] & "*"

But not so your second example:

Between [Forms]![formname]![formfeild] And [Forms]![formname]![formfeild2]

In a (SQL) string in code dates must be bounded with # and must be in ammerican mm/dd/yy format so you need something like:

Between #" & Format([Forms]![formname]![formfeild],"mm/dd/yy&quot & "# And #" & Format([Forms]![formname]![formfeild2],"mm/dd/yy&quot & "# "

Hope this helps

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[FireViking]

Hi Ken,

I am not using code in this instant. I have created the query in the design veiw window and assigned criteria from there. All the 'Like' criteria work fine. The 'Between' dates criteria causes the problem. I did try to write the criteria using your solution in the SQL veiw window but it returns an error. Your suggestion about the format of the data raises a question thou. I hav'nt formatted the data between the form and the qeury and this is where the problem may sit. I am going to attempt to try this a later on.

Thanks for your suggestions

 

[r937]

date literals do have to be bounded by octothorps (pound signs) but they can also be in ISO format, which is not ambiguous

between #2003-01-23# AND #2001-01-25#

another thing to look at is that your date column might have a time component, so you have to watch out for boundary conditions

if formfield1 and formfield2 are the same date, you probably won't find any rows unless the datetime column always has midnight as its time component


rudy

 

[FireViking]

Hi Ken and r937,

You are not going to beleive this but i feel quite embarrassed. My query was working fine right from word go, because the date feilds on the form were blank it returned no records because it was looking for blank feilds ((obviously)). I have constructed a work around to ensure the feilds have data to work with, even if the user leaves them blank.

Thanks to you both for your suggestions, it made me look a bit closer to the data and where it was going before I realised.

cheers all