Ignore blank parametres in query

J

James.90

Registered User.
Local time
Yesterday, 16:12
Joined
Nov 14, 2007
Messages
49
Hey

I have a form where the user can choose from a drop down menu, values like Name= James.90
Date=1/10/07.


Then it would return the record with James.90 And That date in it

But my problem is you have to choose a value for both boxes. Is there a way to ignore it if the field is left blank.

My current code is

Code: 
SELECT timesheet.WeekEnding, timesheet.FName, timesheet.Company, timesheet.Project, timesheet.CTR, timesheet.Comments, timesheet.Total, timesheet.Project_Title
FROM timesheet
WHERE (((timesheet.WeekEnding)=[Forms]![FormBeta]![Week]) AND ((timesheet.Company)=[Forms]![FormBeta]![Company]) AND ((timesheet.Project)=[Forms]![FormBeta]![Project]));
 
I think instead of using '+' you can you 'like' and put an astrick at the end to do this.

Here's a sample:

SELECT Table1.name
FROM Table1
WHERE (((Table1.name) Like [Forms]![Form1]![Text0] & "*"));
 
Make of it what you will. (I spaced it out for readability.)

Code: 
SELECT 
    timesheet.WeekEnding
    ,timesheet.FName
    ,timesheet.Company
    ,timesheet.Project
    ,timesheet.CTR
    ,timesheet.Comments
    ,timesheet.Total
    ,timesheet.Project_Title
FROM 
    timesheet
WHERE 
    (((timesheet.WeekEnding LIKE Nz([Forms]![FormBeta]![Week],"*") AND 
    ((timesheet.Company) LIKE Nz([Forms]![FormBeta]![Company],"*") AND 
    ((timesheet.Project) LIKE Nz([Forms]![FormBeta]![Project],"*"))
;

The difference is that Ken was trying to add an asterisk to a potential NULL value, which won't work since a null value doesn't exist. The Nz function replaces a potential NULL with an asterisk.
 
From this:

(((timesheet.WeekEnding LIKE Nz([Forms]![FormBeta]![Week],"*") AND
((timesheet.Company) LIKE Nz([Forms]![FormBeta]![Company],"*") AND
((timesheet.Project) LIKE Nz([Forms]![FormBeta]![Project],"*"))
;

If all three (weekending, Company, and Project) are text fields, you'll need to give them apostrophe wrappers, like this:

(((timesheet.WeekEnding LIKE '" & Nz([Forms]![FormBeta]![Week],"*") & "' AND
((timesheet.Company) LIKE '" & Nz([Forms]![FormBeta]![Company],"*") & "' AND
((timesheet.Project) LIKE '" & Nz([Forms]![FormBeta]![Project],"*") & "' ))
;

If weekending is a date field, and I think it might be, then the whole thing should look like this:

(((timesheet.WeekEnding LIKE #" & Nz([Forms]![FormBeta]![Week],"*") & "# AND
((timesheet.Company) LIKE '" & Nz([Forms]![FormBeta]![Company],"*") & "' AND
((timesheet.Project) LIKE '" & Nz([Forms]![FormBeta]![Project],"*") & "' ))
;
 
I got the same error as before.

The fields types are all text
 
Copy/Paste the SQL exactly as it appears in SQL View so we can see it. Even better, compact/repair the DB, take out things that are private or unneeded for this problem, and zip it, then send the whole DB.
 
SELECT
timesheet.WeekEnding
,timesheet.FName
,timesheet.Company
,timesheet.Project
,timesheet.CTR
,timesheet.Comments
,timesheet.Total
,timesheet.Project_Title
FROM
timesheet
WHERE
(((timesheet.WeekEnding LIKE '" & Nz([Forms]![FormBeta]![Week],"*") & "' AND
((timesheet.Company) LIKE '" & Nz([Forms]![FormBeta]![Company],"*") & "' AND
((timesheet.Project) LIKE '" & Nz([Forms]![FormBeta]![Project],"*") & "' ))
;
Click to expand...

The database is pretty big and most of it is private as in peoples information but i will see what i can do about uploading a copy of it with the records removed.

Edit i probally won't be able to upload until tomorrow. But thanks for all your help so far. I will post here again tomorrow with the database and hopefully get this solved.
 
I came across this same problem. The way i fixed it was to put each condition in a IF statement, followed by a IsNull check on the Control being used. i.e.
Code: 
strSQL = "SELECT * From table WHERE "
IF NOT IsNull(control name) Then 
       strSQL = strSQL & "(value = value) AND"
IF NOT IsNull(control name) Then 
       strSQL = strSQL & "(value = value) AND"
IF NOT IsNull(control name) Then 
       strSQL = strSQL & "(value = value)"
I then put a block of code which checked the last 4 characters to see if it was the word " AND" (notice the space) and removed the last 4 if was true. I did a similar check to see make sure the string didn't end with WHERE.

this works a charm

oh, and you shouldn't need the ; at the end
 
Hey

I am a bit confused about the last bit of code posted

am i adjusting it properlly

Code: 
strSQL = "SELECT * From timesheet WHERE "
IF NOT IsNull(control name) Then 
       strSQL = strSQL & "(value = value) AND"
IF NOT IsNull(control name) Then 
       strSQL = strSQL & "(value = value) AND"
IF NOT IsNull(control name) Then 
       strSQL = strSQL & "(value = value)"

Also when i run it i get

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
Click to expand...

I attached a copy of my database. Nearly all the data is removed but hopefully you get the idea of the structure.

I bascially want to make it possible to search any field for a parameter and not have to fill in the rest of the boxes

Thanks in advance
 

Attachments

The SQL that i posted is designed to be coded. I have modified the db you posted to include this in the method that I would use.

The db now handles the combo box problem. You will see comments explaining what it does.

I haven't tried to make the dates work. It gives an error on the report when you use a date.
 

Attachments

Sorry for the stupid question.

What did you modfiy on the combo box. I had a look and can't seem to find the comments on what you changed.

It's working great though
 
the comments are in the code...

to get to the code, open the form in design view, right click the Go button, and click Build Event.

This will open the Visual Basic editor, which is where you do all the coding behind access.

Comments are surrounded by ********
 
Hey

I am not sure but it doesn't seem to be working correctly. For example if you leave all the fields blank the record shows up

If i add a new record into the table and search for something in it's record like name=test it still shows the james.90.

Am i doing something wrong. I have gone through the code and read it all to see if i am missing something but i am just checking
 
I am not sure but it doesn't seem to be working correctly. For example if you leave all the fields blank the record shows up
Click to expand...

If you click the button without selecting anything, a message appears telling to you enter something, so im not sure what you mean.

In your original db, you had the combo box values (the ones shown when you click it) to be Values manually entered into the combo box, meaning that they did not reflect the values stored in the timesheet table.

I have changed that in this copy below.
I have set the combo box's to get their values from Beta_Query , and then bound each combo box to the corresponding column.

make sure you are looking at the correct version...
 

Attachments

One last question

You know how the values are pulled from the query which gets the current data from the database. Is it possible to not use the query to get the values from the table and just enter them by hand like in my example or will that mess up your code.

Thanks in advance
 
You must log in or register to reply here.

Similar threads

S
Query to ignore blank cells in table
Replies
11
Views
1,114
Pat Hartman
P
D
Problem with IIF statement and filtering a query
Replies
4
Views
1,163
Pat Hartman
P
asteropi
Solved How to set up unique customer codes based on customer types
2
Replies
31
Views
2,021
jdraw
jdraw
ironfelix717
(DQB) Dynamic Query Builder - Dynamically build VBA queries fast
Replies
9
Views
2,890
Pat Hartman
P
G
How do I access the value of a toggle button in a query statement?
Replies
8
Views
875
theDBguy
theDBguy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom