What is wrong with this code? Everything??

J

jackie

I have tried to correct some code and I really dont know what I'm doing.
I have a form with unbound boxes for dodaac (don't ask), supply chain, and
start and end dates. The user can enter a dodaac and/or a supply chain. That
part works fine. However when I added the date to the mix I couldn't get it
to work for all three searches: dodaac, supply chain and date.
I asked for help and someone sent me some code that does work. It will give
me the start dates and end dates that I enter. Here's the code:
SELECT Jan_08_Orders.[Profit Center], Jan_08_Orders.DoDAAC, [material group]
& " " & [niin] AS NSN, Jan_08_Orders.CreatedOn, Jan_08_Orders.[Order Qty],
Jan_08_Orders.[Shipped Qty], Jan_08_Orders.[Net Value]
FROM Jan_08_Orders
WHERE (((Jan_08_Orders.[Profit Center]) Like "*" &
[Forms]![QBF_form]![supply_chain] & "*") AND ((Jan_08_Orders.DoDAAC) Like "*"
& [Forms]![QBF_form]![dodaac] & "*") AND ((Jan_08_Orders.CreatedOn) Between
[Forms]![QBF_Form]![TxtStartDate] And [Forms]![QBF_Form]![TxtEndDate])) OR
(((Jan_08_Orders.DoDAAC) Like "*" & [Forms]![QBF_form]![dodaac] & "*") AND
((Jan_08_Orders.CreatedOn) Between [Forms]![QBF_Form]![TxtStartDate] And
[Forms]![QBF_Form]![TxtEndDate]) AND (([Forms]![QBf_Form]![supply_chain]) Is
Null)) OR (((Jan_08_Orders.CreatedOn) Between
[Forms]![QBF_Form]![TxtStartDate] And [Forms]![QBF_Form]![TxtEndDate]) AND
(([Forms]![QBf_Form]![dodaac]) Is Null)) OR (((Jan_08_Orders.CreatedOn)
Between [Forms]![QBF_Form]![TxtStartDate] And
[Forms]![QBF_Form]![TxtEndDate]) AND (([Forms]![QBf_Form]![supply_chain]) Is
Null) AND (([Forms]![QBf_Form]![dodaac]) Is Null))
ORDER BY Jan_08_Orders.[Profit Center], Jan_08_Orders.[Order Qty] DESC;

However, I want the user to also be able to filter for dodaac and supply
chain when they enter a date. So they could enter 'Land' as a supply chain,
34399 as a dodaac and then start date 1/1/2007 end date 5/5/2007. I tried
changing the code to this:

SELECT Jan_08_Orders.[Profit Center], Jan_08_Orders.DoDAAC,
Jan_08_Orders.[Material Group], [material group] & " " & [niin] AS NSN,
Jan_08_Orders.CreatedOn, Jan_08_Orders.[Order Qty], Jan_08_Orders.[Shipped
Qty], Jan_08_Orders.[Net Value]
FROM Jan_08_Orders
WHERE (((Jan_08_Orders.[Profit Center]) Like "*" &
[Forms]![QBF_form]![supply_chain] & "*") AND ((Jan_08_Orders.DoDAAC) Like "*"
& [Forms]![QBF_form]![dodaac] & "*") AND ((Jan_08_Orders.CreatedOn) Between
[Forms]![QBF_Form]![TxtStartDate] And [Forms]![QBF_Form]![TxtEndDate])) OR
(((Jan_08_Orders.DoDAAC) Like "*" & [Forms]![QBF_form]![dodaac] & "*") AND
(([Forms]![QBf_Form]![supply_chain]) Is Null)) OR (((Jan_08_Orders.[Profit
Center]) Like "*" & [Forms]![QBF_form]![supply_chain] & "*") AND
(([Forms]![QBf_Form]![dodaac]) Is Null)) OR (((Jan_08_Orders.[Profit Center]
like “*†& [Forms]![QBF_form]![supply_chain] & "*") AND
((Jan_08_Orders.DoDAAC) Like "*"
& [Forms]![QBF_form]![dodaac] & "*") AND ((Jan_08_Orders.CreatedOn) Between
[Forms]![QBF_Form]![TxtStartDate] And [Forms]![QBF_Form]![TxtEndDate]) Is
Null)) OR
(((Jan_08_Orders.CreatedOn) Between [Forms]![QBF_Form]![TxtStartDate] And
[Forms]![QBF_Form]![TxtEndDate]) AND (([Forms]![QBf_Form]![dodaac]) Is Null))
OR
(((Jan_08_Orders.CreatedOn) Between [Forms]![QBF_Form]![TxtStartDate] And
[Forms]![QBF_Form]![TxtEndDate]) AND (([Forms]![QBf_Form]![supply_chain]) Is
Null)) OR
(((Jan_08_Orders.CreatedOn) Between [Forms]![QBF_Form]![TxtStartDate] And
[Forms]![QBF_Form]![TxtEndDate])Is Null OR
((([Forms]![QBf_Form]![supply_chain]) Is Null) AND
(([Forms]![QBf_Form]![dodaac]) Is Null)) And
((Jan_08_Orders.CreatedOn) Between [Forms]![QBF_Form]![TxtStartDate] And
[Forms]![QBF_Form]![TxtEndDate]) Is Null))
ORDER BY Jan_08_Orders.[Profit Center], Jan_08_Orders.[Order Qty] DESC;

I'm getting syntax errors for closing parenthsis, but even I am able to fix
that, I'm nut sure if the code will run. Can someone help me please as I am
under the gun right now. I have been trying to get this date to work for a
few days now. Thanks.
 
J

jackie

Thanks. I fixed it.

jackie said:
I have tried to correct some code and I really dont know what I'm doing.
I have a form with unbound boxes for dodaac (don't ask), supply chain, and
start and end dates. The user can enter a dodaac and/or a supply chain. That
part works fine. However when I added the date to the mix I couldn't get it
to work for all three searches: dodaac, supply chain and date.
I asked for help and someone sent me some code that does work. It will give
me the start dates and end dates that I enter. Here's the code:
SELECT Jan_08_Orders.[Profit Center], Jan_08_Orders.DoDAAC, [material group]
& " " & [niin] AS NSN, Jan_08_Orders.CreatedOn, Jan_08_Orders.[Order Qty],
Jan_08_Orders.[Shipped Qty], Jan_08_Orders.[Net Value]
FROM Jan_08_Orders
WHERE (((Jan_08_Orders.[Profit Center]) Like "*" &
[Forms]![QBF_form]![supply_chain] & "*") AND ((Jan_08_Orders.DoDAAC) Like "*"
& [Forms]![QBF_form]![dodaac] & "*") AND ((Jan_08_Orders.CreatedOn) Between
[Forms]![QBF_Form]![TxtStartDate] And [Forms]![QBF_Form]![TxtEndDate])) OR
(((Jan_08_Orders.DoDAAC) Like "*" & [Forms]![QBF_form]![dodaac] & "*") AND
((Jan_08_Orders.CreatedOn) Between [Forms]![QBF_Form]![TxtStartDate] And
[Forms]![QBF_Form]![TxtEndDate]) AND (([Forms]![QBf_Form]![supply_chain]) Is
Null)) OR (((Jan_08_Orders.CreatedOn) Between
[Forms]![QBF_Form]![TxtStartDate] And [Forms]![QBF_Form]![TxtEndDate]) AND
(([Forms]![QBf_Form]![dodaac]) Is Null)) OR (((Jan_08_Orders.CreatedOn)
Between [Forms]![QBF_Form]![TxtStartDate] And
[Forms]![QBF_Form]![TxtEndDate]) AND (([Forms]![QBf_Form]![supply_chain]) Is
Null) AND (([Forms]![QBf_Form]![dodaac]) Is Null))
ORDER BY Jan_08_Orders.[Profit Center], Jan_08_Orders.[Order Qty] DESC;

However, I want the user to also be able to filter for dodaac and supply
chain when they enter a date. So they could enter 'Land' as a supply chain,
34399 as a dodaac and then start date 1/1/2007 end date 5/5/2007. I tried
changing the code to this:

SELECT Jan_08_Orders.[Profit Center], Jan_08_Orders.DoDAAC,
Jan_08_Orders.[Material Group], [material group] & " " & [niin] AS NSN,
Jan_08_Orders.CreatedOn, Jan_08_Orders.[Order Qty], Jan_08_Orders.[Shipped
Qty], Jan_08_Orders.[Net Value]
FROM Jan_08_Orders
WHERE (((Jan_08_Orders.[Profit Center]) Like "*" &
[Forms]![QBF_form]![supply_chain] & "*") AND ((Jan_08_Orders.DoDAAC) Like "*"
& [Forms]![QBF_form]![dodaac] & "*") AND ((Jan_08_Orders.CreatedOn) Between
[Forms]![QBF_Form]![TxtStartDate] And [Forms]![QBF_Form]![TxtEndDate])) OR
(((Jan_08_Orders.DoDAAC) Like "*" & [Forms]![QBF_form]![dodaac] & "*") AND
(([Forms]![QBf_Form]![supply_chain]) Is Null)) OR (((Jan_08_Orders.[Profit
Center]) Like "*" & [Forms]![QBF_form]![supply_chain] & "*") AND
(([Forms]![QBf_Form]![dodaac]) Is Null)) OR (((Jan_08_Orders.[Profit Center]
like “*†& [Forms]![QBF_form]![supply_chain] & "*") AND
((Jan_08_Orders.DoDAAC) Like "*"
& [Forms]![QBF_form]![dodaac] & "*") AND ((Jan_08_Orders.CreatedOn) Between
[Forms]![QBF_Form]![TxtStartDate] And [Forms]![QBF_Form]![TxtEndDate]) Is
Null)) OR
(((Jan_08_Orders.CreatedOn) Between [Forms]![QBF_Form]![TxtStartDate] And
[Forms]![QBF_Form]![TxtEndDate]) AND (([Forms]![QBf_Form]![dodaac]) Is Null))
OR
(((Jan_08_Orders.CreatedOn) Between [Forms]![QBF_Form]![TxtStartDate] And
[Forms]![QBF_Form]![TxtEndDate]) AND (([Forms]![QBf_Form]![supply_chain]) Is
Null)) OR
(((Jan_08_Orders.CreatedOn) Between [Forms]![QBF_Form]![TxtStartDate] And
[Forms]![QBF_Form]![TxtEndDate])Is Null OR
((([Forms]![QBf_Form]![supply_chain]) Is Null) AND
(([Forms]![QBf_Form]![dodaac]) Is Null)) And
((Jan_08_Orders.CreatedOn) Between [Forms]![QBF_Form]![TxtStartDate] And
[Forms]![QBF_Form]![TxtEndDate]) Is Null))
ORDER BY Jan_08_Orders.[Profit Center], Jan_08_Orders.[Order Qty] DESC;

I'm getting syntax errors for closing parenthsis, but even I am able to fix
that, I'm nut sure if the code will run. Can someone help me please as I am
under the gun right now. I have been trying to get this date to work for a
few days now. Thanks.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

start date and end date in unbound form 1
My query for start and end dates worked for a minute...then stoppe 1
how do text boxes on forms work 1
I know this quesion has been asked a million times but..... 16
Is field between date range on form 2
Add <ALL> to combo box 9
Characters found at end of sql error-Repost 2
Parameterised query to return all records or those Between two val 2

Top