Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Greater Than versus Less Than (Access 2002, SP3)

    Has anyone ever seen the < and the > symbols work differently on one computer than on another? I have the following query:

    SELECT tblSchools.SchoolNum, tblSchools.SchoolName, tblSchools.Phone, tblSchools.Principal, tblCombinedReadiness.StaffVac, tblCombinedReadiness.OutsideWO, tblCombinedReadiness.InsideWO, tblCombinedReadiness.Trailers, tblCombinedReadiness.Trans, tblCombinedReadiness.RevCrisis, tblCombinedReadiness.StatusProj, tblCombinedReadiness.Breakfast, tblCombinedReadiness.SupplyOrd, tblCombinedReadiness.AddConcerns, tblCombinedReadiness.RptDate, tblCombinedReadiness.Area
    FROM tblCombinedReadiness INNER JOIN tblSchools ON tblCombinedReadiness.SchNum = tblSchools.SchoolNum
    WHERE (((tblCombinedReadiness.RptDate)>(Date()-7)));

    I am trying to display all records with a RptDate no older than one week. It works great on my computer but on another computer it displays nothing unless I change the greater than symbol to a less than symbol, which makes no sense at all. It is as if the second computer has reversed the meaning of these two symbols.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Greater Than versus Less Than (Access 2002, SP3)

    That is *very* strange. The query as posted is correct if you want to include records with RptDate no older than one week. There are no reasonable explanations why < and > would have reversed meanings.

    Have you checked whether the system date on the problem PC is set correctly? If the system date were set to a date in (for example) 2008, the 'correct' query might well return no records.
    Also, have you checked whether it is a temporary glitch - does the problem persist if you reboot the problem PC?

  3. #3
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Greater Than versus Less Than (Access 2002, SP3)

    I would also check the Windows settings for the date format. 11th August is one of those dates where the UK setting and UK setting will make it either 11th August or 8th November.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Greater Than versus Less Than (Access 2002, SP3)

    Why can't you change the data type?

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Greater Than versus Less Than (Access 2002, SP3)

    As I investigated further, I found that the RptDate field which is being appended from an Excel spreadsheet is actually a text field. I hadn't noticed that previously because it worked as I expected on my computer. I tried changing the field in the query to ReportDateateValue(rptDate) but then when I use the criteria >(Date()-7) I get the message that says "Data type mismatch in criteria expression". Without changing the data type is there any way I can get this to work?

    Another thing I noticed is that the computer it works on is using version 2002 and the computer where it does not work is version 2003. I even tried it on another computer using version 2003. The difference is apparently a different way a date in a text field is treated in these two versions of Access.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Greater Than versus Less Than (Access 2002, SP3)

    Could you test on a copy of the database what happens if you change the data type to Date, then append Excel data? If it works correctly, you may have solved your problem.

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Greater Than versus Less Than (Access 2002, SP3)

    Because I have users appending data from Excel files that have been emailed to them into this table and I thought it would stop working if I made a change to the data type. However since nobody is working right now I did a test and it worked fine. THANKS FOR THE IDEA.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Greater Than versus Less Than (Access 2002, SP3)

    Since I am working after hours and nobody is here I tried that and it worked great. Thanks again for your patience and help. Now I won't fret about this all weekend.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Greater Than versus Less Than (Access 2002, SP

    Hi Judy

    I have been watching this on and off during the day and now that you have come found the problem, I was thinking that you could get around the problem by creating a make table query from the original source table and then convert the text formatted date to a date/time field.

    Alternatively try Hans' approach of changing the receiving tables field to date/time. You are in one of those infuriating situations where the data does not seem to play ball. I used the two I have described on a couple of occasions each working differently each time. Can I also just check if you are actually appending from an XLS and not a CSV? (just checking as I have had issues with date/time nulls before but didn't want to mudy the problem yet)
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •