Between Date Function Issue (1 Viewer)

[NSAMSA]

I'm having an odd issue where my date will not recognize the year when I use "Between" in my query. Just a quick rundown of the issue:

The original date from the ODBC connection is in a short text format. I used CDate to convert it to Date/Time. However, now when I type in a month after using between [start date] and [end date], it brings back all records of the particular month. For example, if I put in 3/2/2019, I'll get back entries from 3/2/2017, 3/2/2016 and so on. I'm confused as to why this is doing this, and thus far have not figured out a solution. Has anyone else experienced anything like this, and if so, how was it fixed?

Regards

 

[plog]

Please post the full SQL.

 

[NSAMSA]

SELECT dbo_tblIO_Rej.dtoDateTime, dbo_tblIO_Rej.LineID, dbo_tblIO_Rej.EquipmentID, dbo_tblIO_Rej.Inspected, dbo_tblIO_Rej.Rejected, dbo_tblIO_Rej.DayNoUTC, CDate(Left([dtoDateTime],10)) AS RejDate, Left(Right([dtoDateTime],15),8) AS DynTime, CDate(IIf([DynTime]<"08:00:00",[RejDate]-1,[RejDate])) AS PlantDate
FROM (dbo_tblSite INNER JOIN dbo_tblLine ON dbo_tblSite.SiteID = dbo_tblLine.SiteID) INNER JOIN dbo_tblIO_Rej ON dbo_tblLine.LineID = dbo_tblIO_Rej.LineID
WHERE (((dbo_tblSite.SiteID)=31));


The one I want to do the between function with is the Plant Date.

 

[plog]

I don't see your BETWEEN criteria. Please post the SQL that is giving you issues.

Strike that, you can't do that. You can't use a field you create in the SELECT in the WHERE. You either need to use another query based on this one, or use the expression that creates PlantDate in the WHERE of this one.

 

[NSAMSA]

That would be this one:

SELECT [01-qryHENEQP].LineName, [01-qryHENEQP].LocalText, [02-qryIOReject].Inspected, [02-qryIOReject].Rejected, [02-qryIOReject].PlantDate
FROM [01-qryHENEQP] INNER JOIN [02-qryIOReject] ON ([01-qryHENEQP].EquipmentID = [02-qryIOReject].EquipmentID) AND ([01-qryHENEQP].LineID = [02-qryIOReject].LineID)
WHERE ((([01-qryHENEQP].LineName) Like "*" & [shop] & "*") AND (([02-qryIOReject].PlantDate) Between [start date] And [end date]));

 

[plog]

That query requires 2 parameters be input, your initial post said you supplied "3/2/2019" and got unexpected results.

Can you post your database (stripped down to just the objects I need) so that I can recreate your issue?

 

[MajP]

Also use this or something similar
http://www.dpriver.com/pp/sqlformat.htm

SELECT dbo_tblio_rej.dtodatetime, 
       dbo_tblio_rej.lineid, 
       dbo_tblio_rej.equipmentid, 
       dbo_tblio_rej.inspected, 
       dbo_tblio_rej.rejected, 
       dbo_tblio_rej.daynoutc, 
       Cdate(LEFT([dtodatetime], 10))                               AS RejDate, 
       LEFT(RIGHT([dtodatetime], 15), 8)                            AS DynTime, 
       Cdate(Iif([dyntime] < "08:00:00", [rejdate] - 1, [rejdate])) AS PlantDate 
FROM   (dbo_tblsite 
        INNER JOIN dbo_tblline 
                ON dbo_tblsite.siteid = dbo_tblline.siteid) 
       INNER JOIN dbo_tblio_rej 
               ON dbo_tblline.lineid = dbo_tblio_rej.lineid 
WHERE  (( ( dbo_tblsite.siteid ) = 31 ));

A whole lot easier to read.