Query Design w/ VBA

[jammy1812]

Hi guys,

I'm building a query, but can't quite get the coding to work.

I have 3 items I've selected for this query:

ContractDeliveryDate
AmmendedContractDeliveryDate
CompletionDate

I'm trying to make a query that display any orders that are within 7 WORKING days of today's date, but with a few considerations.

If the "CompletionDate" has a date in it, then that column should not be included in the report.

If the "AmmendedContractDeliveryDate" is blank, then it compares the date of "ContractDeliveryDate" and the date in working days time.

If "AmmendedContractDeliveryDate" has a date in it, then it compares THAT instead of "ContractDeliveryDate".

This is what I've got so far:

[/FONT][/I][/FONT]
[FONT=Calibri][I][FONT=Verdana]iif(isnull([CompletionDate]) = False Then [/FONT][/I][/FONT]
[FONT=Calibri][I][FONT=Calibri][FONT=Verdana]iif(isnull([AmmendedContractDeliveryDate]) = True Then[/FONT][/FONT][/I][/FONT]
[FONT=Calibri][I][FONT=Calibri][FONT=Verdana]Between AmmendedContractDeliveryDate() And DateAdd("d",7,Date()) [/FONT][/FONT][/I][/FONT]
[FONT=Calibri][I][FONT=Calibri][FONT=Verdana]Else[/FONT][/FONT][/I][/FONT]
[FONT=Calibri][FONT=Calibri][FONT=Calibri][FONT=Calibri][FONT=Verdana][I]iif(isnull([AmmendedContractDeliveryDate]) = False Then[/I][/FONT][/FONT][/FONT]
[FONT=Calibri][FONT=Calibri][/FONT][/FONT][FONT=Verdana][I]ContractDeliveryDate() And DateAdd("d",7,Date()) [/I][/FONT]
[FONT=Verdana][I]

Any help? I'll be exporting this to Excel, but will worry about that later!

Thanks
[/FONT]

 

[spikepl]

Design your query in the grid of the query designer for each permutation. In the SQL-view of the query designer you then have the SQL the way it is suppose to look like, when generated by your VBA for each casse/permutation. Then make the VBA write the SQL exactly like that.

 

[jammy1812]

Hey, thanks for the reply but I don't really follow you.
I've got the grid open, but not sure what to type and where?

I've clicked the three instances, but how do I get them to show what I want based on the data in the fields?

 

[spikepl]

If you do not know how to build a query in the query designer then that would be the first thing to bring in order, because you will need this to check your VBA-built query anyway.

Find an Access query tutorial on the web, or perhaps someone has a recommended link.

 

[jammy1812]

I know how to build a query in query designer, but I'm not sure what to put in as my criteria for selecting/displaying results.

So where in query designer would I write the code (if my above code is correct) to ensure that any entries with "CompletionDate" selected, would not show up in the query?

 

[spikepl]

You make a query for each permutation of your conditions. If you do not know how to apply conditions in the design grid do a tutorial. Test the query and when it runs fine, then you have one SQL statement for each given set of conditions.

Then, in VBA, with each set of conditions, you need to generate the same SQL statement as yiou got before with that same set of conditions,.

Update: Have I got confused by your title , and you are actually not building the SQL in VBA code?