Date Range Input in Criteria Not Working

[toadmaster]

Hello,

I would truely appreciate it if I could have some input with this issue.

I have an access query, I am trying to get it to filter the results based on the date input that pops up when the query is run.

Currently when I run it with the relevant dates entered nothing is returned.

SELECT AP.QN, GI.SN, 
   AP.AT, AP.PE, 
   GI.TR, AP.RP, 
   GI.CT, 
   Max(IIf(Len(NZ([PDT],0))=1,
      Null,CDate(IIf(Len([PDT])=5,
         Left(CStr([PDT]),1),Left(CStr([PDT]),2))
            & "/" & Left(Right(CStr([PDT]),4),2)
            & "/" & Right(CStr([PDT]),2)))) 
     AS [AP DATE], 
   Max(IIf(Len(NZ([ATO],0))=1,Null,
      CDate(IIf(Len([ATO])=5,
      Left(CStr([ATO]),1),Left(CStr([ATO]),2)) 
         & "/" & Left(Right(CStr([ATO]),4),2)
         & "/" & Right(CStr([ATO]),2)))) 
     AS [FD DATE], [LN - NAME].COR
FROM (GI 
   INNER JOIN AP 
      ON GI.NO = AP.QN) 
   INNER JOIN [LN - NAME] 
      ON GI.SN = [LN - NAME].SNM
GROUP BY AP.QN, GI.SN, 
   AP.AT, AP.PE, 
   GI.TR, AP.RP, 
   GI.CT, [LN - NAME].COR
HAVING (((Max(IIf(Len(NZ([PDT],0))=1,
      Null,CDate(IIf(Len([PDT])=5,
         Left(CStr([PDT]),1),
            Left(CStr([PDT]),2)) & "/" 
             & Left(Right(CStr([PDT]),4),2) 
             & "/" & Right(CStr([PDT]),2)))))=
                [START MM/DD/YYYY])
       AND ((Max(IIf(Len(NZ([ATO],0))=1,Null,
               CDate(IIf(Len([ATO])=5,
               Left(CStr([ATO]),1),Left(CStr([ATO]),2)) 
                     & "/" & Left(Right(CStr([ATO]),4),2)
                     & "/" & Right(CStr([ATO]),2)))))=
                        [END MM/DD/YYYY]))
ORDER BY Max(IIf(Len(NZ([PDT],0))=1,
   Null,CDate(IIf(Len([PDT])=5,
      Left(CStr([PDT]),1),
         Left(CStr([PDT]),2)) & "/" & 
            Left(Right(CStr([PDT]),4),2) 
              & "/" & Right(CStr([PDT]),2))))

Thank you all so much

[MMcCarthy]

Instead of ...

Left(CStr([PDT]),1),Left(CStr([PDT]),2)) & "/" & Left(Right(CStr([PDT]),4),2) & "/" & Right(CStr([PDT]),2)))) AS [AP DATE]

can I suggest using ...

DateSerial(Left(Right(CStr([PDT]),4),2),Left(CStr([PDT]),1),Left(CStr([PDT]),2)),Right(CStr([PDT]),2))))

I think your problem is that your dates are not being recognised as dates.

[toadmaster]

Thank you MMcCarthy for your response

Unfortunately the reason why I had to resort to using

 Left(CStr([PDT]),1),Left(CStr([PDT]),2)) & "/" & Left(Right(CStr([PDT]),4),2) & "/" & Right(CStr([PDT]),2)))) AS [AP DATE]

is because my dates in the database are being stored as 22609 (mddyy) and I need them to be displayed as mm/dd/yyyy if I use your suggestion I am getting 2/22/2026 instead of 2/26/2009.

The thing is if I apply

BETWEEN [START DATE MM/DD/YYYY] AND [END DATE MM/DD/YYYY]

to the AP Date Column only it works fine refining the query to the specified date criteria, however it doesn't work when I apply [START DATE MM/DD/YYYY] to AP and then apply the End date [END DATE MM/DD/YYYY] to the FD Date column, that is when it's not returning anything in the query. I am wondering if the issue is because I am filtering between two different columns AP DATE and FD DATE?

Hope I have explained myself better :) and thank you for all your time

[toadmaster]

I also noticed that if I search dates with the least amount of data required to fulfill the criteria it works e.g. if I search between 09/01/2010 and 09/02/2010 the query works correctly however if I search 09/01/2010 and 10/01/2010 then it does not return anything even though it should.

[MMcCarthy]

Based on your last post it sounds like you are having a formatting problem with the dates. It's reading them in dd/mm/yy format not mm/dd/yy. Try imposing a format ...

Format(Left(CStr([PDT]),1),Left(CStr([PDT]),2)) & "/" & Left(Right(CStr([PDT]),4),2) & "/" & Right(CStr([PDT]),2)))),"mm/dd/yyyy") AS [AP DATE]

[malcolmk]

it wont read between two adjascent dates because there is no date betwen them if you get what I mean try startdate-1 to enddate+1. Its like saying what number comes between 2 and 3?
Thats what I figure is happening.

[toadmaster]

MMcCarthy and MalcolmK thank you for your help with this matter. I got the code working without having to change the formatting of the date, I had to make some changes to the SQL. I will post my updated code here, just in case someone else is having a similar problem. But thanks you for all your help.