Pass paramter to SQL Criteria - Is Not Null And <Date()-1 -

[Lateral]

Hi Guys


I have tried to figure this out but can't so I'm hoping I'm missing something easy that somebody will help me solve.


I have a query that basically chooses records from a table that contains a list of Invoices with a field, [InvoiceDate] based upon the following criteria:


Is Not Null And <Date()-1


This works well.


I have another table, "ConfigurationInformation" that has a single record that contains the various configuration and setup parameters for the the application.


It contains a field called [NumberOfDays].


I want to pass the contents of the [NumberOfDays] field the Date so that it looks something like the following:


Is Not Null And <Date()- [NumberOfDays]


I just can't figure it out.


Thanks for any help you can provide.


Cheers
Greg

 

[JHB]

Have you included the table "ConfigurationInformation" in your query?
Show the whole SQL string.

 

[Lateral]

Hi JHB,


Sorry for not providing the entire query but it is quite big and I was trying to explain the issue more simply.


Yes, I did include the table in my query but it displayed the attached error message.


Here is the query as it currently stands:


SELECT Workorders.WorkorderID, Customers.ContactFirstName, Customers.ContactLastName, Customers.MobNumber, Customers.EmailAddress, Workorders.DateReceived, [Sum Of Payments Query].[Total Payments], Customers.CustomerID, Workorders.Date_WO_Sent, Workorders.ProblemDescription, Workorders.DatePickedUp, Format([Parts Total],"Currency") AS WOTotal, [Parts Total]-[Total Payments] AS [Total Owing], Workorders.WorkOrderType, [Parts Totals by Workorder Money Owing].*, Workorders.PurchaseOrder, Workorders.ReminderCount, Workorders.DateRequestDeposit, Workorders.DateRequestBalance, Workorders.Valid, Workorders.CancelledDate, Workorders.ExcludeFromAutoReminder, Int([DatePickedUp]) AS ReminderDateCheck, tWorkorderType.WorkOrderTypeDesc, DateDiff("d",[DatePickedUp],Date()) AS Expr1
FROM tWorkorderType INNER JOIN (Customers INNER JOIN ((Workorders LEFT JOIN [Sum Of Payments Query] ON Workorders.WorkorderID = [Sum Of Payments Query].WorkorderID) INNER JOIN [Parts Totals by Workorder Money Owing] ON Workorders.WorkorderID = [Parts Totals by Workorder Money Owing].WorkorderID) ON Customers.CustomerID = Workorders.CustomerID) ON tWorkorderType.ID = Workorders.WorkOrderType
WHERE ((([Sum Of Payments Query].[Total Payments])="$0.00") AND ((Customers.CustomerID)<>51 And (Customers.CustomerID)<>1) AND ((Workorders.Date_WO_Sent) Is Not Null Or (Workorders.Date_WO_Sent)<Date()-[NumberOfDays]) AND ((Workorders.DatePickedUp) Is Null) AND (([Parts Total]-[Total Payments])>0) AND ((Workorders.PurchaseOrder)<>Yes) AND ((Workorders.Valid)<>No) AND ((Workorders.ExcludeFromAutoReminder)<>Yes) AND ((tWorkorderType.WorkOrderTypeDesc) Like "*Parts and Other Items*" Or (tWorkorderType.WorkOrderTypeDesc) Like "*Wheels*"))
ORDER BY Workorders.WorkorderID DESC;



Cheers
Greg

 

[JHB]

Sorry, I can't see you've included the table "ConfigurationInformation" in your query, from where you should get the "[NumberOfDays]"?
Could you post your database with some sample data, (zip it) + name of your query.

 

[Lateral]

Hi JHB,


I'm in the process of stripping down my database into a smaller version. I have also switched to using the DateDiff function in the query...give me a few minutes.

 

[Lateral]

I'm back!


Ok, I have attached a small sample database with the following:


1. Table - Workorders
2. Table - My Company Information

3. Query - Workorders Query
4. Form - fWorkorders


In the query, you will see where I am using the following to filter the records in the Workorders table:


WoDays: DateDiff("d",[Date_WO_Sent],Date())


Is Null Or >=2


There is a field in the table "My Company Information" called [AutoRemindersDays] that contains the number of days I wish to use in place of the number "2" in the Is Null Or >= 2 criteria.

I hope I have made myself clear with the above explanation.

Cheers

 

[JHB]

I change value of [AutoRemindersDays]=6 so you can see the result.

 

[Lateral]

Thanks JHB but adding the My Company Information table to the actual complex query that I am using causes the error message that I attached in my previous post....

 

[JHB]

Is the table all ready added, (and is the table actually called "My Company Information").?

 

[JHB]

Another way is to create a query for table "My Company Information", see attached example.

 

[Lateral]

Same error message and the table is called My Company Information

 

[JHB]

Sorry, if you can't figure out, then I think the only way to help you is to get the real query (and tables)!

 

[Lateral]

I will put together a cut down version of the database tomorrow and get it to you...thanks for helping me with this.


Cheers

 

[CJ_London]

just like to point out

Is Null Or >=2

the is null is irrelevant, if Date_WO_Sent is null, the datediff function will fail with an error before it even gets to the criteria

 

[Lateral]

Hi CJ,


I'm not seeing any errors.


How would I change the code to handle this type of condition?

 

[CJ_London]

presumably you are not seeing errors because Date_WO_Sent is always populated.

how you handle it if it can happen depends, but typically you would use the nz function

 

[Lateral]

The Date_WO_Sent is not always populated that is why I was checking for nulls...I'll have a play with the NZ function

 

[CJ_London]

if you want nulls always to be shown use

DateDiff("d",nz([Date_WO_Sent],Date()),Date())

and if you don't

DateDiff("d",nz([Date_WO_Sent],Date()+3),Date())

(think I've got that the right way round

 

[Lateral]

Thanks mate