Query Criteria (1 Viewer)

[Emma35]

Hi All,
I have a query which is causing me some problems. I've attached a snapshot of the design view. I'd like the query results to be as follows

1. If i choose nothing then i get all the results in the table (CTMain)
2. If i choose a Plant i see all results for that plant only
3. If i choose a Location...same as above
4. If i choose 2 plants (using the OR function in the criteria) i'd like to see all the results from both Plants...same applies to Location

I'd certainly appreciate any help you could offer
Thanks,
Em

 

[CJ_London]

Always better to copy/paste the full sql and surround with code tags rather than a screenshot. Then we can copy paste the names of your fields/formulae when responding

1. If i choose nothing then i get all the results in the table (CTMain)
2. If i choose a Plant i see all results for that plant only
3. If i choose a Location...same as above

the standard way to do this is

WHERE (myfield=forms!myform!myField OR forms!myform!myField is null) AND (myfield2=forms!myform!myField2 OR forms!myform!myField2 is null)

4. If i choose 2 plants (using the OR function in the criteria) i'd like to see all the results from both Plants...same applies to Location

all depends on how you are identifying the two plants/locations in your form.

 

[Emma35]

Ok sorry i should have posted the SQL. Thanks for your help with this

SELECT tbl_CTMain.DateRaised, tbl_CTMain.Plant, tbl_CTMain.Location
FROM tbl_CTMain
WHERE (((tbl_CTMain.DateRaised)>=[Forms]![frm_DLFilter]![txtD1] And (tbl_CTMain.DateRaised)<=[Forms]![frm_DLFilter]![txtD2]) AND ((tbl_CTMain.Plant) Like "*" & [Forms]![frm_DLFilter]![cboPlant] & "*") AND ((tbl_CTMain.Location) Like "*" & [Forms]![frm_DLFilter]![cboBlock] & "*"));

 

[CJ_London]

thanks - so try this. I've taken out all the unnecessary brackets and table names to make it easier to read - the query builder will put them back in when you save it

SELECT DateRaised, Plant, Location
FROM tbl_CTMain
WHERE (DateRaised>=[Forms]![frm_DLFilter]![txtD1] OR [Forms]![frm_DLFilter]![txtD1] is null) And (DateRaised<=[Forms]![frm_DLFilter]![txtD2]  OR [Forms]![frm_DLFilter]![txtD2] is null) AND (Plant Like "*" & [Forms]![frm_DLFilter]![cboPlant] & "*" OR [Forms]![frm_DLFilter]![cboPlant] is null) & "*" AND (Location Like "*" & [Forms]![frm_DLFilter]![cboBlock] & "*"  OR [Forms]![frm_DLFilter]![cboBlock] is null);

I'm a bit confused by your form - if you are selecting from comboboxes you should have an exact match in which case I would expect to use

SELECT DateRaised, Plant, Location
FROM tbl_CTMain
WHERE (DateRaised>=[Forms]![frm_DLFilter]![txtD1] OR [Forms]![frm_DLFilter]![txtD1] is null) And (DateRaised<=[Forms]![frm_DLFilter]![txtD2]  OR [Forms]![frm_DLFilter]![txtD2] is null) AND (Plant =[Forms]![frm_DLFilter]![cboPlant]  OR [Forms]![frm_DLFilter]![cboPlant] is null) & "*" AND (Location = [Forms]![frm_DLFilter]![cboBlock] OR [Forms]![frm_DLFilter]![cboBlock] is null);

information not provided for your 4th requirement

also in sql you are passing dates as string when referencing the form (it is a textbox control), date formats should be in the US format of mm/dd/yyyy and the string should be surrounded by the # character to tell sql that what is between them is to be treated as a date in which case you would have

SELECT DateRaised, Plant, Location
FROM tbl_CTMain
WHERE (DateRaised>="#" & format ([Forms]![frm_DLFilter]![txtD1],"mm/dd/yyyy") & "#" OR [Forms]![frm_DLFilter]![txtD1] is null) And (DateRaised<="#" & format([Forms]![frm_DLFilter]![txtD2],"mm/dd/yyyy") & "#" OR [Forms]![frm_DLFilter]![txtD2] is null) AND (Plant =[Forms]![frm_DLFilter]![cboPlant]  OR [Forms]![frm_DLFilter]![cboPlant] is null) & "*" AND (Location = [Forms]![frm_DLFilter]![cboBlock] OR [Forms]![frm_DLFilter]![cboBlock] is null);

If you already use the US format then you don't need the format function but you still need the # chars

dates are stored as a special form of decimal number, what you see is based on the format property. So today is 43649 and now (11:52am) is 43649.4944212963 where the bit after the decimal point is the based on the number of seconds to now for the day, divided by 86400 (the number of seconds in a day)

The reason for mentioning this is because if your dateraised field is populated with the now function then it will include a time element, even if you can't see it due to the format. So your code

DateRaised<="#" & format([Forms]![frm_DLFilter]![txtD2],"mm/dd/yyyy") & "#"

will 'fail' if txtD2 is say 30th June 2019 and dateraised is 30th June 2019 at 11:52 because 43646.4944212963 is greater than 43646

 

[Emma35]

Thanks again for your time with this.....i actually haven't a clue what i'm looking at when viewing code so would it be possible to show me how to do this just using Design View in the query ?. Sorry for being a dunce

I swapped your code for my own and it all looks like a bit of a mess now

 

[CJ_London]

just copy and paste the code into a new query sql window, then select design view - benefit of providing the code

 

[Emma35]

Ok i've pasted it code in but it looks like nothing i've ever seen in Design View. When i try the query, the dates function works but the Location and Plant just return all records.

 

[CJ_London]

that comes from freetyping - had left a ' & "*" ' in there. try this

SELECT DateRaised, Plant, Location
FROM tbl_CTMain
WHERE (DateRaised>="#" & format ([Forms]![frm_DLFilter]![txtD1],"mm/dd/yyyy") & "#" OR [Forms]![frm_DLFilter]![txtD1] is null) And (DateRaised<="#" & format([Forms]![frm_DLFilter]![txtD2],"mm/dd/yyyy") & "#" OR [Forms]![frm_DLFilter]![txtD2] is null) AND (Plant =[Forms]![frm_DLFilter]![cboPlant]  OR [Forms]![frm_DLFilter]![cboPlant] is null) AND (Location = [Forms]![frm_DLFilter]![cboBlock] OR [Forms]![frm_DLFilter]![cboBlock] is null);

 

[Emma35]

Tried the new code but getting an error message attached

 

[CJ_London]

did you just copy/paste the code

 

[CJ_London]

my bad - I'm used to writing and running queries in VBA - when referencing a text box in a query you don't need the # chars or it would appear the format function either

try this

SELECT DateRaised, Plant, Location
FROM tbl_CTMain
WHERE (DateRaised>=[Forms]![frm_DLFilter]![txtD1]  OR [Forms]![frm_DLFilter]![txtD1] is null)  And (DateRaised<= [Forms]![frm_DLFilter]![txtD2] OR [Forms]![frm_DLFilter]![txtD2] is null) AND (Plant =[Forms]![frm_DLFilter]![cboPlant]  OR [Forms]![frm_DLFilter]![cboPlant] is null) AND (Location = [Forms]![frm_DLFilter]![cboBlock] OR [Forms]![frm_DLFilter]![cboBlock] is null);

 

[Emma35]

Thanks again...i'm hopeless with code sorry. Your amended code works fine for the dates and the plant but returns nothing for the Block. I tried adding in a second plant to your code but it doesn't work....i need to be able to select 2 plants. Sorry this is turning into a marathon

SELECT DateRaised, Plant, Location
FROM tbl_CTMain
WHERE (DateRaised>=[Forms]![frm_DLFilter]![txtD1] OR [Forms]![frm_DLFilter]![txtD1] is null) And (DateRaised<= [Forms]![frm_DLFilter]![txtD2] OR [Forms]![frm_DLFilter]![txtD2] is null) AND (Plant =[Forms]![frm_DLFilter]![cboPlant] OR [Forms]![frm_DLFilter]![cboPlant] is null) AND (Plant =[Forms]![frm_DLFilter]![cboPlant2] OR [Forms]![frm_DLFilter]![cboPlant2] is null) AND (Location = [Forms]![frm_DLFilter]![cboBlock] OR [Forms]![frm_DLFilter]![cboBlock] is null);

 

[CJ_London]

but returns nothing for the Block

sounds like you may be using lookup fields in your table. If so remove them - it is likely that whatever you have set the bound column to in your table is not the same as the value stored in your table - lookups in tables hide this

I tried adding in a second plant to your code but it doesn't work....i need to be able to select 2 plants

as I said in post #4, need more information - basically how are you selecting two (or more) plants? two combos? multiselect listbox? something else?

 

[Emma35]

I get the Block from a combo box which looks at a table which contains about 20 different options but i don't have lookup fields in the table itself.

I'm using a second combo box to select the second plant. Would a multiselect listbox be a better option ?

 

[CJ_London]

in your main table what is the location field populated with? and do the values match what you have in your block table?

 

[Emma35]

It is populated with 8 different locations in short text format. Yes they do match.

Edit: I inherited this database second hand and it's a mess. I know the names don't coincide which is making things more complicated

 

[CJ_London]

and are your plant values also text?

and to be clear, since your cboBlock contains 20 values, and your table only 8, you are selecting one of the 8?

 

[Emma35]

Yes everything is set as text. Sorry i was a bit confused....cboBlock is looking at a table of possible blocks which i set up to populate the combo box. This was just for getting results from the query which looks at the main table

 

[CJ_London]

and the answer to my other question?

and to be clear, since your cboBlock contains 20 values, and your table only 8, you are selecting one of the 8?

 

[Emma35]

There is a table of values which i set up to populate the combo box cboBlock and this is used to feed the query which looks at the main table. Incidentally a block is a work area and a plant is a smaller section within that area.