Data Mismatch with date (1 Viewer)

[Kayleigh]

Hi,
I have written a query to find out all dates within last week, using a date commencing field. This worked very well:

SELECT tblOrders.fldOrderID
FROM tblOrders INNER JOIN qryAddressTradeRetail ON tblOrders.fldOAddressID = qryAddressTradeRetail.fldAddressID
WHERE (((DateAdd("d",-Weekday([fldOCreated])+1,[fldOCreated]))>Date()-7) AND ((tblOrders.fldOStatusID)=10) AND ((qryAddressTradeRetail.fldCTradeRetailID)=2) AND ((tblOrders.fldOSupplyFitID)=1));

I then tried implementing with another similar query but got a date mismatch when trying to add criteria to the date commencing field. (Although this worked fine above.)

SELECT tblOrders.fldOrderID, tblOrders.fldOTotalQuote, tblOrders.fldOSoldDate, DateAdd("d",-Weekday([fldOSoldDate])+1,[fldOSoldDate]) AS DateCommencing
FROM tblOrders INNER JOIN qryAddressTradeRetail ON tblOrders.fldOAddressID = qryAddressTradeRetail.fldAddressID
WHERE (((tblOrders.fldOStatusID)=8 Or (tblOrders.fldOStatusID)=11 Or (tblOrders.fldOStatusID)=13 Or (tblOrders.fldOStatusID)=14 Or (tblOrders.fldOStatusID)=15) AND ((tblOrders.fldOSoldDate) Is Not Null) AND ((qryAddressTradeRetail.fldCTradeRetailID)=2) AND ((tblOrders.fldOSupplyFitID)=1));

Any ideas why this is happening?

 

[MajP]

In your source query do you format fldosolddate by chance or convert it by some other means so that it is not a date? If you did then convert it back.

DateAdd("d",-Weekday(cdate([fldOSoldDate]))+1,cdate([fldOSoldDate]))

 

[Kayleigh]

Thanks - worked on that to create expression which works. Here is query in SQL:

SELECT tblOrders.fldOrderID, tblOrders.fldOStatusID, tblOrders.fldOTotalQuote, IIf(IsDate([fldOSoldDate]),DateValue(DateAdd("d",-Weekday(CDate([fldOSoldDate]))+1,CDate([fldOSoldDate]))),Null) AS WeekCommence, tblOrders.fldOSoldDate
FROM tblOrders INNER JOIN qryAddressTradeRetail ON tblOrders.fldOAddressID = qryAddressTradeRetail.fldAddressID
WHERE (((tblOrders.fldOStatusID)=8 Or (tblOrders.fldOStatusID)=11 Or (tblOrders.fldOStatusID)=13 Or (tblOrders.fldOStatusID)=14 Or (tblOrders.fldOStatusID)=15) AND ((IIf(IsDate([fldOSoldDate]),DateValue(DateAdd("d",-Weekday(CDate([fldOSoldDate]))+1,CDate([fldOSoldDate]))),Null))>Date()-7) AND ((tblOrders.fldOSoldDate) Is Not Null) AND ((qryAddressTradeRetail.fldCTradeRetailID)=2) AND ((tblOrders.fldOSupplyFitID)=1));

Now can you help with changing into VBA so it can be written as part of code. So far...

Private Sub txtSalesWk_DblClick(Cancel As Integer)
On Error Resume Next

Dim ODate As Date
If IsDate([fldOSoldDate]) Then
    ODate = DateValue(DateAdd("d", -Weekday(CDate([fldOSoldDate])) + 1, CDate([fldOSoldDate])))
End If

Dim varWhere As Variant

If IsNothing(Me.txtSalesWk) Then Exit Sub

varWhere = "(((tblOrders.fldOStatusID)=8 Or (tblOrders.fldOStatusID)=11 Or (tblOrders.fldOStatusID)=13 Or (tblOrders.fldOStatusID)=14 Or (tblOrders.fldOStatusID)=15) AND ((ODate)>Date()-7) AND ((tblOrders.fldOSupplyFitID)=1))"
varWhere = varWhere & " AND (tblClients.fldCTradeRetailID) = " & 2

DoCmd.OpenForm "frmOrderMan", , , varWhere

End Sub

Thank you

 

[MajP]

Can you do a
debug.print varwhere
Before the docmd.openform line?
Then post to show what it resolves.

 

[Kayleigh]

Printed:
(((tblOrders.fldOStatusID)=8 Or (tblOrders.fldOStatusID)=11 Or (tblOrders.fldOStatusID)=13 Or (tblOrders.fldOStatusID)=14 Or (tblOrders.fldOStatusID)=15) AND ((ODate)>Date()-7) AND ((tblOrders.fldOSupplyFitID)=1)) AND (tblClients.fldCTradeRetailID) = 2


Also requests parameter value for ODate...

 

[The_Doc_Man]

Also requests parameter value for ODate...

I'll reply to the narrow question: You have an SQL string referencing ODate but the problem is that it isn't defined where SQL can see it. You have defined ODate in VBA. Whenever you see a request for a parameter (and you didn't intentionally define the query as a Parameter query), it means you have either misspelled the thing being requested OR it is defined in a way that is not visible to VBA.

You see, there are TWO environments in Access. There is the GUI/VBA environment which is part of the Access development environtment, but then there is the SQL environment that is part of a separate "helper" task, the ACE database engine. Different physical process under Windows, so their memory elements don't significantly overlap very much if at all. When you try to execute a query (either db.Execute or DoCmd.RunSQL), eventually Access passes along the SQL string to ACE so that it can separately work on developing the results for you.

In your specific case, ODate is not visible to SQL and therefore you get the parameter question. To be visible, ODate would have to be a field in a table OR you would have to build your SQL string to use string substitution to incorporate the variable's value as though it were a literal constant.

Your SQL statement that looks like

... AND ((ODate)>Date()-7) AND ...

should actually look like this in the place where you build the WHERE clause

"...AND ((#" & FormatDateTime( ODate, vbShortDate ) ) & ")>Date()-7) AND..."

FormatDateTime function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

Note that "vbShortDate" implies that it will interact with your Windows regional date settings.

HOWEVER... your logic for that SQL is a bunch of ANDed sub-clauses including that sub-clause involving ODate, and therefore your entire query depends on two external values - ODate and Date() - which you could compare OUTSIDE of the SQL and decide from that logic whether to run the query at all. The way you have it written, ODate appears to be constant for the current routine activation and at least for a given date, so is Date(). So why does this appear in the query at all?

 

[MajP]

If odate exists in both tables of your inner join then you need tablename.fieldname and it needs to exist in the query

 

[Kayleigh]

Thanks for clarifying. Really this shouldn't need to be so complicated, as I've written similar functions before without an issue. It's only because I'm trying to find records where OSoldDate is in the last week and it seems that this field is not a straightforward date value hence all the 'wrapper' code.
Since this code is behind an unbound form, the fields are being called only once the openForm "frmOrderMan" command has triggered so fields are found in that form. But when I added the if ... code to declare the variable, it can't find this field on the current form.
Any ideas how to convert the OSoldDate date value within the code?