Results 1 to 15 of 15

Unable to use Date()-1 as Criteria

  1. #1
    UPSDuder is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2019
    Posts
    11

    Unable to use Date()-1 as Criteria

    I am trying to automate a report and have one last piece of the puzzle. I need to update the criteria for one of my "Make Table" Queries to the previous days date. If I manually put in the date, the query runs properly (pictured here).

    Click image for larger version. 

Name:	Capture.JPG 
Views:	26 
Size:	26.6 KB 
ID:	37351

    However; if I am to use "Date()-1" within the Criteria I receive the error:



    Click image for larger version. 

Name:	Capture1.JPG 
Views:	26 
Size:	27.2 KB 
ID:	37352

    I'm open to try any method to achieve updating to the previous day for criteria. Is it possible to have the date within a table pushed to here ? When I tried to do this in SQL, it wiped out all of the links to all of the tables.

    Please share any thoughts on how I could accomplish this.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,496
    ??What are the dashes in front of the table names?

    Can you post the SQL for the query?

  3. #3
    UPSDuder is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2019
    Posts
    11
    I didn't build this database, I just acquired it... here a screenshot of the names

    Click image for larger version. 

Name:	Capture3.JPG 
Views:	25 
Size:	11.6 KB 
ID:	37354

    SQL View:

    SELECT [- Standard Calendar].[Day Date], [- Operational Organization Current Perspective Detail].[Region Num], [- Operational Organization Current Perspective Detail].[District Num], [- Operational Organization Current Perspective Detail].[Center Num], [- Package Pickup Detail].[Pickup Letterbox Source Num], [- Package Pickup Detail].[DIAD Orgnl Stop Acct Name], [- Package Pickup Detail].[Pickup Disposition Status Desc], [- Package Pickup Detail].[PU Info Status Reason Desc], [- Package Pickup Detail].[Pickup Letterbox Ind], [- Package Pickup Detail].[Pickup Disposition Status Cd], [- Stop - Pickup Acct Mgmnt Detail].[PAM Acct Num], [- Package Stop Basic Common Detail].[Stop Start Time], [- Package Stop Basic Common Detail].[Stop Complete Time], [- Package Stop Basic Common Detail].[Stop Complete Time HHMMSS], [- Stop Dispatch Timecard Detail].[DIAD Orgnl Pkg Driver Name] INTO Tbl_CutClosedCheck
    FROM [- Stop Pickup Detail] INNER JOIN (([- Package Stop Basic Common Detail] INNER JOIN (([- Operational Organization Current Perspective Detail] INNER JOIN ([- Standard Calendar] INNER JOIN [- Package Pickup Detail] ON [- Standard Calendar].[ODBC Join Key] = [- Package Pickup Detail].[ODBC Join Key]) ON [- Operational Organization Current Perspective Detail].[ODBC Join Key] = [- Package Pickup Detail].[ODBC Join Key]) INNER JOIN [- Stop - Pickup Acct Mgmnt Detail] ON [- Operational Organization Current Perspective Detail].[ODBC Join Key] = [- Stop - Pickup Acct Mgmnt Detail].[ODBC Join Key]) ON [- Package Stop Basic Common Detail].[ODBC Join Key] = [- Stop - Pickup Acct Mgmnt Detail].[ODBC Join Key]) INNER JOIN [- Stop Dispatch Timecard Detail] ON [- Package Stop Basic Common Detail].[ODBC Join Key] = [- Stop Dispatch Timecard Detail].[ODBC Join Key]) ON [- Stop Pickup Detail].[ODBC Join Key] = [- Standard Calendar].[ODBC Join Key]
    WHERE ((([- Standard Calendar].[Day Date])=#2/7/2019#) AND (([- Operational Organization Current Perspective Detail].[Region Num])="03" Or ([- Operational Organization Current Perspective Detail].[Region Num])="07" Or ([- Operational Organization Current Perspective Detail].[Region Num])="10") AND (([- Package Pickup Detail].[DIAD Orgnl Stop Acct Name]) Not Like "@@") AND (([- Package Pickup Detail].[Pickup Letterbox Ind])="1"));

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,948
    You could try DateAdd("d", -1, Date()) or DateAdd("d", -1, Date)
    Have to admit I didn't look at all the sql because those table names are just insane.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    So, like, why do you, like, start every sentence with, like, "so"?

  5. #5
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,579
    Why to you provide the sql to code that does work, and not the code that doesn't work?

    I suggest it is because (in addition to the very poor table names) you have spaces in your field names and the field names contain reserved words like 'date', day', 'name'. Generally speaking you need to surround these with square brackets - in the query grid, it is not sufficient for it to be in sql.

  6. #6
    UPSDuder is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2019
    Posts
    11
    Here is the SQL that has the error I posted above

    SELECT [- Standard Calendar].[Day Date], [- Operational Organization Current Perspective Detail].[Region Num], [- Operational Organization Current Perspective Detail].[District Num], [- Operational Organization Current Perspective Detail].[Center Num], [- Package Pickup Detail].[Pickup Letterbox Source Num], [- Package Pickup Detail].[DIAD Orgnl Stop Acct Name], [- Package Pickup Detail].[Pickup Disposition Status Desc], [- Package Pickup Detail].[PU Info Status Reason Desc], [- Package Pickup Detail].[Pickup Letterbox Ind], [- Package Pickup Detail].[Pickup Disposition Status Cd], [- Stop - Pickup Acct Mgmnt Detail].[PAM Acct Num], [- Package Stop Basic Common Detail].[Stop Start Time], [- Package Stop Basic Common Detail].[Stop Complete Time], [- Package Stop Basic Common Detail].[Stop Complete Time HHMMSS], [- Stop Dispatch Timecard Detail].[DIAD Orgnl Pkg Driver Name] INTO Tbl_CutClosedCheck
    FROM [- Stop Pickup Detail] INNER JOIN (([- Package Stop Basic Common Detail] INNER JOIN (([- Operational Organization Current Perspective Detail] INNER JOIN ([- Standard Calendar] INNER JOIN [- Package Pickup Detail] ON [- Standard Calendar].[ODBC Join Key] = [- Package Pickup Detail].[ODBC Join Key]) ON [- Operational Organization Current Perspective Detail].[ODBC Join Key] = [- Package Pickup Detail].[ODBC Join Key]) INNER JOIN [- Stop - Pickup Acct Mgmnt Detail] ON [- Operational Organization Current Perspective Detail].[ODBC Join Key] = [- Stop - Pickup Acct Mgmnt Detail].[ODBC Join Key]) ON [- Package Stop Basic Common Detail].[ODBC Join Key] = [- Stop - Pickup Acct Mgmnt Detail].[ODBC Join Key]) INNER JOIN [- Stop Dispatch Timecard Detail] ON [- Package Stop Basic Common Detail].[ODBC Join Key] = [- Stop Dispatch Timecard Detail].[ODBC Join Key]) ON [- Stop Pickup Detail].[ODBC Join Key] = [- Standard Calendar].[ODBC Join Key]
    WHERE ((([- Standard Calendar].[Day Date])=Date()-1) AND (([- Operational Organization Current Perspective Detail].[Region Num])="03" Or ([- Operational Organization Current Perspective Detail].[Region Num])="07" Or ([- Operational Organization Current Perspective Detail].[Region Num])="10") AND (([- Package Pickup Detail].[DIAD Orgnl Stop Acct Name]) Not Like "@@") AND (([- Package Pickup Detail].[Pickup Letterbox Ind])="1"));

  7. #7
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,579
    have you tried putting square brackets around Day Date in the query grid?

  8. #8
    UPSDuder is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2019
    Posts
    11
    Quote Originally Posted by Ajax View Post
    have you tried putting square brackets around Day Date in the query grid?
    WHERE ((([- Standard Calendar].[Day Date])=Date()-1) AND

    Did not work, was greeted with

  9. #9
    UPSDuder is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2019
    Posts
    11
    Posting error from message above
    Click image for larger version. 

Name:	Capture4.JPG 
Views:	12 
Size:	17.4 KB 
ID:	37396

  10. #10
    UPSDuder is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2019
    Posts
    11
    I receive this text box when placing brackets around Date()-1
    Click image for larger version. 

Name:	Capture1.JPG 
Views:	12 
Size:	19.3 KB 
ID:	37397

  11. #11
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,579
    I wasn't suggesting putting square brackets round the Date()-1, but around Day Date in the query grid. I presume that is what you tried per post #8. Since you posted the sql, I can't tell whether you did or not.

    Your error would appear to relate to transaction management. Your query is a make table query which uses transaction management behind the scenes.

    Personally I would at least rename fields going into your new table

    As stated before, spaces and non standard characters in names can produce misleading errors.

  12. #12
    UPSDuder is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2019
    Posts
    11
    Quote Originally Posted by Ajax View Post
    I wasn't suggesting putting square brackets round the Date()-1, but around Day Date in the query grid. I presume that is what you tried per post #8. Since you posted the sql, I can't tell whether you did or not.

    Your error would appear to relate to transaction management. Your query is a make table query which uses transaction management behind the scenes.

    Personally I would at least rename fields going into your new table

    As stated before, spaces and non standard characters in names can produce misleading errors.
    Yes I tried that, unfortunately same result.

  13. #13
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,579
    which? query grid or renaming?

    I have to go now, will be out for 24 hours

  14. #14
    UPSDuder is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2019
    Posts
    11
    Query grid, I am unable to rename the tables due to them being linked to an oracle database.

  15. #15
    hammerman is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Jul 2014
    Posts
    29

    DateAdd("d", ????, Date())

    Micron's snippet works. DateAdd("d", -1, Date()) or DateAdd("d", -1, Date). You can even even automate this to allow any different input other than '-1' by putting a prompt within square brackets, ( eg: DataAdd("d", ["How many days ago? (use negative sign)"], Date()) . Caveat: If you use this in SQL, be sure to use (single vs double) quotation marks correctly.
    I use this to enter specific dates into a table with >1500 records, in batches, based on when something happened to whichever item the record refers to.

    You can also change the referenced date within the function to something other than ('today's') "Date()", though that is the most common reference, I would think. (E.g, you can reference that change/DateAdd(,,) to another date within the record, using another instance of the DateAdd() function, or even within the code/SQL that is changing the current information. For instance, change this record's date, and set a marker to revisit the record in a month, a year or some number of days hence.

    Last edited by hammerman; 02-11-2019 at 11:39 AM. Reason: clarify idea details

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 01-30-2019, 04:55 PM
  2. Unable to format the date in report
    By donsi in forum Reports
    Replies: 1
    Last Post: 09-01-2016, 11:00 AM
  3. Replies: 2
    Last Post: 04-02-2015, 11:45 AM
  4. Unable to use date time picker
    By jhargram in forum Forms
    Replies: 1
    Last Post: 11-22-2012, 09:07 AM
  5. Replies: 3
    Last Post: 08-21-2012, 02:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums