Help with the All Option in a Combo Box on a Form (access 2000)

[abrighs]

I have been through tons of sites looking for solutions for my problem, being
unable to find any, I have decided to post one myself.
I am currently working on one end of a project. I have a form based on a
table. This form has two text boxes for entering the start and end date, 2
combo boxes for selecting the shift, and 3 buttons for performing functions
with the data entered. The user clicks a button when they are done making
their entries/selections on the form. Each button is related to a report
that is based on a query. The query takes the start and end dates entered
(and any date in between), and the letters in the combo box (shifts) to
create a report that shows all data gathered for the specific dates and the
shifts requested.
My problem is that I have 3 Shifts. I have not found away to be able to use
3 combo boxes to make the selections because the query criteria, Is Not Null
And Between [forms]![frmLHLSVSR-DD]![combo1] And [forms]![frmLHLSVSR-DD]!
[combo2], will not let me use 3 combo boxes (or does not seem to want to).
From the reading that I have done I will need to include an All Option in my
combo box so that the User can print the data from all of the shifts. I can
do this and get the All to show up in the combo box, but the report comes out
blank.
In the end I want the user to be able to specify their dates on the form,
pick the shifts they want to show the data for (currently they can use A D,
or A, M etc, but they can not do A,D,M whcih is the goal) and have it all
show up on the report. I believe this is one of my final steps for this
problem so any help that you could offer would be great. I also wasn't sure
what code to provide you with, so if you are requiring any, please let me
know.
Thank you
Sarah

 

[Guest]

You should be able to not select a combobox, therefore a null, and the query
ignore it.
Post your query.

 

[abrighs via AccessMonster.com]

Hello
Thank you for your reply. This is the query:
SELECT dbo_tblPLCDriverData.Date_Posted, dbo_tblPLCDriverData.Time_Posted,
dbo_tblPLCDriverData.Shift, dbo_tblPLCTagXRef.Common_Name,
dbo_tblPLCDriverData.Tag_ID, dbo_tblPLCDriverData.Data, dbo_tblPLCTagXRef.
Unit_of_Measure
FROM dbo_tblPLCDriverData INNER JOIN dbo_tblPLCTagXRef ON
dbo_tblPLCDriverData.Tag_ID = dbo_tblPLCTagXRef.Tag_ID
WHERE (((dbo_tblPLCDriverData.Date_Posted) Is Not Null And
(dbo_tblPLCDriverData.Date_Posted) Between [forms]![frmLHLSVSR-DD]![startdate]
And [forms]![frmLHLSVSR-DD]![enddate]) AND ((dbo_tblPLCDriverData.Shift) Is
Not Null And (dbo_tblPLCDriverData.Shift) Between [forms]![frmLHLSVSR-DD]!
[combo1] And [forms]![frmLHLSVSR-DD]![combo2]) AND ((dbo_tblPLCDriverData.
Data)<>0));
I did try to leave them blank, and didn't get any results back on the report.
I figured this was because of my Is Not Null statement. I took that out and
tried again and still no avail.
I do thank you for your help though!

You should be able to not select a combobox, therefore a null, and the query
ignore it.
Post your query.

I have been through tons of sites looking for solutions for my problem, being
unable to find any, I have decided to post one myself.

[quoted text clipped - 24 lines]

Thank you
Sarah

 

[Guest]

Try this --
SELECT dbo_tblPLCDriverData.Date_Posted, dbo_tblPLCDriverData.Time_Posted,
dbo_tblPLCDriverData.Shift, dbo_tblPLCTagXRef.Common_Name,
dbo_tblPLCDriverData.Tag_ID, dbo_tblPLCDriverData.Data,
dbo_tblPLCTagXRef.Unit_of_Measure
FROM dbo_tblPLCDriverData INNER JOIN dbo_tblPLCTagXRef ON
dbo_tblPLCDriverData.Tag_ID = dbo_tblPLCTagXRef.Tag_ID
WHERE (((dbo_tblPLCDriverData.Date_Posted) Is Not Null And
(dbo_tblPLCDriverData.Date_Posted) Between
IIF([forms]![frmLHLSVSR-DD]![startdate] Is Null, “#1/1/1901#â€,
[forms]![frmLHLSVSR-DD]![startdate]) And
IIF([forms]![frmLHLSVSR-DD]![enddate] Is Null,
“#12/31/2099#â€,[forms]![frmLHLSVSR-DD]![enddate])) AND
((dbo_tblPLCDriverData.Shift) Is Not Null And (dbo_tblPLCDriverData.Shift)
Between IIF([forms]![frmLHLSVSR-DD]![combo1] Is Null, “Aâ€,
[forms]![frmLHLSVSR-DD]![combo1]) And IIF([forms]![frmLHLSVSR-DD]![combo2]
Is Null, “Mâ€, [forms]![frmLHLSVSR-DD]![combo2])) AND
((dbo_tblPLCDriverData.Data)<>0));

Hello
Thank you for your reply. This is the query:
SELECT dbo_tblPLCDriverData.Date_Posted, dbo_tblPLCDriverData.Time_Posted,
dbo_tblPLCDriverData.Shift, dbo_tblPLCTagXRef.Common_Name,
dbo_tblPLCDriverData.Tag_ID, dbo_tblPLCDriverData.Data, dbo_tblPLCTagXRef.
Unit_of_Measure
FROM dbo_tblPLCDriverData INNER JOIN dbo_tblPLCTagXRef ON
dbo_tblPLCDriverData.Tag_ID = dbo_tblPLCTagXRef.Tag_ID
WHERE (((dbo_tblPLCDriverData.Date_Posted) Is Not Null And
(dbo_tblPLCDriverData.Date_Posted) Between [forms]![frmLHLSVSR-DD]![startdate]
And [forms]![frmLHLSVSR-DD]![enddate]) AND ((dbo_tblPLCDriverData.Shift) Is
Not Null And (dbo_tblPLCDriverData.Shift) Between [forms]![frmLHLSVSR-DD]!
[combo1] And [forms]![frmLHLSVSR-DD]![combo2]) AND ((dbo_tblPLCDriverData.
Data)<>0));
I did try to leave them blank, and didn't get any results back on the report.
I figured this was because of my Is Not Null statement. I took that out and
tried again and still no avail.
I do thank you for your help though!

You should be able to not select a combobox, therefore a null, and the query
ignore it.
Post your query.

I have been through tons of sites looking for solutions for my problem, being
unable to find any, I have decided to post one myself.

[quoted text clipped - 24 lines]

Thank you
Sarah

 

[abrighs via AccessMonster.com]

Hello
I am still testing that code that you sent me. I have now created a test
form, report, and query so that I don't modify too much of the project before
I know exactly where I am going. I don't know if it will work yet, when I
put that in my SQL view for my query this is what I have:
SELECT dbo_tblPLCDriverData.Date_Posted, dbo_tblPLCDriverData.Time_Posted,
dbo_tblPLCDriverData.Shift, dbo_tblPLCTagXRef.Common_Name,
dbo_tblPLCDriverData.Tag_ID, dbo_tblPLCDriverData.Data, dbo_tblPLCTagXRef.
Unit_of_Measure
FROM dbo_tblPLCDriverData INNER JOIN dbo_tblPLCTagXRef ON
dbo_tblPLCDriverData.Tag_ID = dbo_tblPLCTagXRef.Tag_ID
WHERE (((dbo_tblPLCDriverData.Date_Posted) Is Not Null And
(dbo_tblPLCDriverData.Date_Posted) Between IIf([forms]![atest]![startdate] Is
Null,#1/1/1901#,[forms]![atest]![startdate]) And IIf([forms]![atest]![enddate]
Is Null,#12/31/2099#,[forms]![atest]![enddate])) AND ((dbo_tblPLCDriverData.
Shift) Is Not Null And (dbo_tblPLCDriverData.Shift) Between IIf([forms]!
[atest]![combo1] Is Null,[“A”],[forms]![atest]![combo1]) And IIf([forms]!
[atest]![combo2] Is Null,[“M”],[forms]![atest]![combo2])) AND (
(dbo_tblPLCDriverData.Data)<>0));
When I enter my start date, end date, make the selections from the list box
and then click on the preview report button (now report = atest) I get
parameter boxes asking for a value for "A" and "M" I am not sure what to
write in here so I have not been able to see my report yet! (and I also
wonder why it doesn't ask for "D" if this is in relation to the shift codes!)
Do you have any idea how I can get rid of this pop up boxes? Then I could
test the rest to make sure it is working properly. Again thank you for all
of your help.

Try this --
SELECT dbo_tblPLCDriverData.Date_Posted, dbo_tblPLCDriverData.Time_Posted,
dbo_tblPLCDriverData.Shift, dbo_tblPLCTagXRef.Common_Name,
dbo_tblPLCDriverData.Tag_ID, dbo_tblPLCDriverData.Data,
dbo_tblPLCTagXRef.Unit_of_Measure
FROM dbo_tblPLCDriverData INNER JOIN dbo_tblPLCTagXRef ON
dbo_tblPLCDriverData.Tag_ID = dbo_tblPLCTagXRef.Tag_ID
WHERE (((dbo_tblPLCDriverData.Date_Posted) Is Not Null And
(dbo_tblPLCDriverData.Date_Posted) Between
IIF([forms]![frmLHLSVSR-DD]![startdate] Is Null, “#1/1/1901#”,
[forms]![frmLHLSVSR-DD]![startdate]) And
IIF([forms]![frmLHLSVSR-DD]![enddate] Is Null,
“#12/31/2099#”,[forms]![frmLHLSVSR-DD]![enddate])) AND
((dbo_tblPLCDriverData.Shift) Is Not Null And (dbo_tblPLCDriverData.Shift)
Between IIF([forms]![frmLHLSVSR-DD]![combo1] Is Null, “A”,
[forms]![frmLHLSVSR-DD]![combo1]) And IIF([forms]![frmLHLSVSR-DD]![combo2]
Is Null, “M”, [forms]![frmLHLSVSR-DD]![combo2])) AND

Hello
Thank you for your reply. This is the query:

[quoted text clipped - 24 lines]

 

[abrighs via AccessMonster.com]

Yeah I just posted and then noticed that I made mistake. I meant to say
combo box, not list box, 8 lines from the bottom!

Hello
I am still testing that code that you sent me. I have now created a test
form, report, and query so that I don't modify too much of the project before
I know exactly where I am going. I don't know if it will work yet, when I
put that in my SQL view for my query this is what I have:
SELECT dbo_tblPLCDriverData.Date_Posted, dbo_tblPLCDriverData.Time_Posted,
dbo_tblPLCDriverData.Shift, dbo_tblPLCTagXRef.Common_Name,
dbo_tblPLCDriverData.Tag_ID, dbo_tblPLCDriverData.Data, dbo_tblPLCTagXRef.
Unit_of_Measure
FROM dbo_tblPLCDriverData INNER JOIN dbo_tblPLCTagXRef ON
dbo_tblPLCDriverData.Tag_ID = dbo_tblPLCTagXRef.Tag_ID
WHERE (((dbo_tblPLCDriverData.Date_Posted) Is Not Null And
(dbo_tblPLCDriverData.Date_Posted) Between IIf([forms]![atest]![startdate] Is
Null,#1/1/1901#,[forms]![atest]![startdate]) And IIf([forms]![atest]![enddate]
Is Null,#12/31/2099#,[forms]![atest]![enddate])) AND ((dbo_tblPLCDriverData.
Shift) Is Not Null And (dbo_tblPLCDriverData.Shift) Between IIf([forms]!
[atest]![combo1] Is Null,[“A”],[forms]![atest]![combo1]) And IIf([forms]!
[atest]![combo2] Is Null,[“M”],[forms]![atest]![combo2])) AND (
(dbo_tblPLCDriverData.Data)<>0));
When I enter my start date, end date, make the selections from the list box
and then click on the preview report button (now report = atest) I get
parameter boxes asking for a value for "A" and "M" I am not sure what to
write in here so I have not been able to see my report yet! (and I also
wonder why it doesn't ask for "D" if this is in relation to the shift codes!)
Do you have any idea how I can get rid of this pop up boxes? Then I could
test the rest to make sure it is working properly. Again thank you for all
of your help.

Try this --
SELECT dbo_tblPLCDriverData.Date_Posted, dbo_tblPLCDriverData.Time_Posted,

[quoted text clipped - 20 lines]

 

[Guest]

It has added brackets where they should not be --
Between IIf([forms]![atest]![combo1] Is Null,[“Aâ€],[forms]![atest]![combo1])
And IIf([forms]![atest]![combo2] Is Null,[“Mâ€],[forms]![atest]![combo2]))

Make it read --
Between IIf([forms]![atest]![combo1] Is Null,“Aâ€,[forms]![atest]![combo1])
And IIf([forms]![atest]![combo2] Is Null,“Mâ€,[forms]![atest]![combo2]))

Hello
I am still testing that code that you sent me. I have now created a test
form, report, and query so that I don't modify too much of the project before
I know exactly where I am going. I don't know if it will work yet, when I
put that in my SQL view for my query this is what I have:
SELECT dbo_tblPLCDriverData.Date_Posted, dbo_tblPLCDriverData.Time_Posted,
dbo_tblPLCDriverData.Shift, dbo_tblPLCTagXRef.Common_Name,
dbo_tblPLCDriverData.Tag_ID, dbo_tblPLCDriverData.Data, dbo_tblPLCTagXRef.
Unit_of_Measure
FROM dbo_tblPLCDriverData INNER JOIN dbo_tblPLCTagXRef ON
dbo_tblPLCDriverData.Tag_ID = dbo_tblPLCTagXRef.Tag_ID
WHERE (((dbo_tblPLCDriverData.Date_Posted) Is Not Null And
(dbo_tblPLCDriverData.Date_Posted) Between IIf([forms]![atest]![startdate] Is
Null,#1/1/1901#,[forms]![atest]![startdate]) And IIf([forms]![atest]![enddate]
Is Null,#12/31/2099#,[forms]![atest]![enddate])) AND ((dbo_tblPLCDriverData.
Shift) Is Not Null And (dbo_tblPLCDriverData.Shift) Between IIf([forms]!
[atest]![combo1] Is Null,[“Aâ€],[forms]![atest]![combo1]) And IIf([forms]!
[atest]![combo2] Is Null,[“Mâ€],[forms]![atest]![combo2])) AND (
(dbo_tblPLCDriverData.Data)<>0));
When I enter my start date, end date, make the selections from the list box
and then click on the preview report button (now report = atest) I get
parameter boxes asking for a value for "A" and "M" I am not sure what to
write in here so I have not been able to see my report yet! (and I also
wonder why it doesn't ask for "D" if this is in relation to the shift codes!)
Do you have any idea how I can get rid of this pop up boxes? Then I could
test the rest to make sure it is working properly. Again thank you for all
of your help.

Try this --
SELECT dbo_tblPLCDriverData.Date_Posted, dbo_tblPLCDriverData.Time_Posted,
dbo_tblPLCDriverData.Shift, dbo_tblPLCTagXRef.Common_Name,
dbo_tblPLCDriverData.Tag_ID, dbo_tblPLCDriverData.Data,
dbo_tblPLCTagXRef.Unit_of_Measure
FROM dbo_tblPLCDriverData INNER JOIN dbo_tblPLCTagXRef ON
dbo_tblPLCDriverData.Tag_ID = dbo_tblPLCTagXRef.Tag_ID
WHERE (((dbo_tblPLCDriverData.Date_Posted) Is Not Null And
(dbo_tblPLCDriverData.Date_Posted) Between
IIF([forms]![frmLHLSVSR-DD]![startdate] Is Null, “#1/1/1901#â€,
[forms]![frmLHLSVSR-DD]![startdate]) And
IIF([forms]![frmLHLSVSR-DD]![enddate] Is Null,
“#12/31/2099#â€,[forms]![frmLHLSVSR-DD]![enddate])) AND
((dbo_tblPLCDriverData.Shift) Is Not Null And (dbo_tblPLCDriverData.Shift)
Between IIF([forms]![frmLHLSVSR-DD]![combo1] Is Null, “Aâ€,
[forms]![frmLHLSVSR-DD]![combo1]) And IIF([forms]![frmLHLSVSR-DD]![combo2]
Is Null, “Mâ€, [forms]![frmLHLSVSR-DD]![combo2])) AND

Hello
Thank you for your reply. This is the query:

[quoted text clipped - 24 lines]

Thank you
Sarah

 

[abrighs via AccessMonster.com]

i did take out those brackets and i get the same problem. It wants me to
enter a parmeter for "A" or "M"
This is what the query looks like now:

SELECT dbo_tblPLCDriverData.Date_Posted, dbo_tblPLCDriverData.Time_Posted,
dbo_tblPLCDriverData.Shift, dbo_tblPLCTagXRef.Common_Name,
dbo_tblPLCDriverData.Tag_ID, dbo_tblPLCDriverData.Data, dbo_tblPLCTagXRef.
Unit_of_Measure
FROM dbo_tblPLCDriverData INNER JOIN dbo_tblPLCTagXRef ON
dbo_tblPLCDriverData.Tag_ID = dbo_tblPLCTagXRef.Tag_ID
WHERE (((dbo_tblPLCDriverData.Date_Posted) Is Not Null And
(dbo_tblPLCDriverData.Date_Posted) Between IIf([forms]![atestcombo]!
[startdate] Is Null,#1/1/1901#,[forms]![atestcombo]![startdate]) And IIf(
[forms]![atestcombo]![enddate] Is Null,#12/31/2099#,[forms]![atestcombo]!
[enddate])) AND ((dbo_tblPLCDriverData.Shift) Is Not Null And
(dbo_tblPLCDriverData.Shift) Between IIf([forms]![atestcombo]![combo1] Is
Null,“A”,[forms]![atestcombo]![combo1]) And IIf([forms]![atestcombo]![combo2]
Is Null,“M”,[forms]![atestcombo]![combo2])) AND ((dbo_tblPLCDriverData.Data)
<>0));

thanks again, i am starting to feel like a real pain!

It has added brackets where they should not be --
Between IIf([forms]![atest]![combo1] Is Null,[“A”],[forms]![atest]![combo1])
And IIf([forms]![atest]![combo2] Is Null,[“M”],[forms]![atest]![combo2]))

Make it read --
Between IIf([forms]![atest]![combo1] Is Null,“A”,[forms]![atest]![combo1])
And IIf([forms]![atest]![combo2] Is Null,“M”,[forms]![atest]![combo2]))

Hello
I am still testing that code that you sent me. I have now created a test

[quoted text clipped - 48 lines]

 

[Guest]

Open the form and fill in everything but do not hit the Go button.

Run the query and see if it prompts. If it does not then the prompts are
comming from the form and not the query.

i did take out those brackets and i get the same problem. It wants me to
enter a parmeter for "A" or "M"
This is what the query looks like now:

SELECT dbo_tblPLCDriverData.Date_Posted, dbo_tblPLCDriverData.Time_Posted,
dbo_tblPLCDriverData.Shift, dbo_tblPLCTagXRef.Common_Name,
dbo_tblPLCDriverData.Tag_ID, dbo_tblPLCDriverData.Data, dbo_tblPLCTagXRef.
Unit_of_Measure
FROM dbo_tblPLCDriverData INNER JOIN dbo_tblPLCTagXRef ON
dbo_tblPLCDriverData.Tag_ID = dbo_tblPLCTagXRef.Tag_ID
WHERE (((dbo_tblPLCDriverData.Date_Posted) Is Not Null And
(dbo_tblPLCDriverData.Date_Posted) Between IIf([forms]![atestcombo]!
[startdate] Is Null,#1/1/1901#,[forms]![atestcombo]![startdate]) And IIf(
[forms]![atestcombo]![enddate] Is Null,#12/31/2099#,[forms]![atestcombo]!
[enddate])) AND ((dbo_tblPLCDriverData.Shift) Is Not Null And
(dbo_tblPLCDriverData.Shift) Between IIf([forms]![atestcombo]![combo1] Is
Null,“Aâ€,[forms]![atestcombo]![combo1]) And IIf([forms]![atestcombo]![combo2]
Is Null,“Mâ€,[forms]![atestcombo]![combo2])) AND ((dbo_tblPLCDriverData.Data)
<>0));

thanks again, i am starting to feel like a real pain!

It has added brackets where they should not be --
Between IIf([forms]![atest]![combo1] Is Null,[“Aâ€],[forms]![atest]![combo1])
And IIf([forms]![atest]![combo2] Is Null,[“Mâ€],[forms]![atest]![combo2]))

Make it read --
Between IIf([forms]![atest]![combo1] Is Null,“Aâ€,[forms]![atest]![combo1])
And IIf([forms]![atest]![combo2] Is Null,“Mâ€,[forms]![atest]![combo2]))

Hello
I am still testing that code that you sent me. I have now created a test

[quoted text clipped - 48 lines]

Thank you
Sarah