Page 2 of 2 FirstFirst 12
Results 16 to 29 of 29
  1. #16
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    missing arguments

    DateSerial(Year(Date), Month(Date) + IIf(Day(Date) < 19, -1, 0), 18) And DateSerial(Year(Date), Month(Date) + IIf(Day(Date) < 19, 0, 1), 18)

  2. #17
    Join Date
    May 2020
    Posts
    26
    Quote Originally Posted by davegri View Post
    missing arguments

    DateSerial(Year(Date), Month(Date) + IIf(Day(Date) < 19, -1, 0), 18) And DateSerial(Year(Date), Month(Date) + IIf(Day(Date) < 19, 0, 1), 18)
    This one gives a data mismatch error

  3. #18
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Quote Originally Posted by Breath of Fresh Air View Post
    This one gives a data mismatch error
    That's odd. Works perfectly on my setup. Do you have a field or object named DATE? That's a reserved word in Access.

  4. #19
    Join Date
    May 2020
    Posts
    26
    Quote Originally Posted by davegri View Post
    That's odd. Works perfectly on my setup. Do you have a field or object named DATE? That's a reserved word in Access.
    No nothing at all. And also the experession that you gave me first works fine (except the calculation) The expression that you first gave me works with no errors I mean.

  5. #20
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Maybe it's the context within which it's used. Can you provide some insight on that?

  6. #21
    Join Date
    Apr 2017
    Posts
    1,673
    There was missed Date function in my example formulas (and abundant brackets for Date functions, but Access must remove them automatically). Here is an example of working test procedure
    Code:
    Public Sub Test()
        
        Dim datTest1 As Date
        Dim datTest2 As Date
        
        datTest1 = DateSerial(Year(Date), Month(Date) + IIf(Day(Date) < 18, -1, 0), 18)
        datTest2 = DateSerial(Year(Date), Month(Date) + IIf(Day(Date) < 18, 0, 1), 18)
        
    End Sub

  7. #22
    Join Date
    May 2020
    Posts
    26
    Quote Originally Posted by ArviLaanemets View Post
    There was missed Date function in my example formulas (and abundant brackets for Date functions, but Access must remove them automatically). Here is an example of working test procedure
    Code:
    Public Sub Test()
        
        Dim datTest1 As Date
        Dim datTest2 As Date
        
        datTest1 = DateSerial(Year(Date), Month(Date) + IIf(Day(Date) < 18, -1, 0), 18)
        datTest2 = DateSerial(Year(Date), Month(Date) + IIf(Day(Date) < 18, 0, 1), 18)
        
    End Sub
    HBi , Thanks for that. Unfortunately this gives a data mismatch error which is puzzling. Should the second line have a different < sign?

  8. #23
    Join Date
    May 2020
    Posts
    26
    Quote Originally Posted by davegri View Post
    Maybe it's the context within which it's used. Can you provide some insight on that?
    I did think of that , and so created a new data base with made up table and data with the same result.

    So I have a table with records that have a date field. Creating a query that extracts the records according to the expression that we are writing here.
    What is puzzling is that the first one you did works as far as the data is concerned, it just needs the extra Iif expression

  9. #24
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I did think of that , and so created a new data base with made up table and data with the same result.
    Can you post that DB here, to provide a non-working example?

  10. #25
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Breath of Fresh Air View Post
    HBi , Thanks for that. Unfortunately this gives a data mismatch error which is puzzling. Should the second line have a different
    Maybe the problem is with you having Office365? I tested procedure with MS Access 2010, in wired LAN and Office installed in PC.

  11. #26
    Join Date
    May 2020
    Posts
    26
    Quote Originally Posted by ArviLaanemets View Post
    Maybe the problem is with you having Office365? I tested procedure with MS Access 2010, in wired LAN and Office installed in PC.
    Could be I suppose. I will try it at home on Office 2013 and let you know.

  12. #27
    Join Date
    May 2020
    Posts
    26
    Quote Originally Posted by Breath of Fresh Air View Post
    Could be I suppose. I will try it at home on Office 2013 and let you know.
    Having spent far too much time than the problem warrented I manged to sort it out and get an understanding of the < logic you used.

    What I ended up with was :-
    Between DateSerial(Year(Date()),Month(Date())+IIf(Day(Date ())<18,-1,0),18) And DateSerial(Year(Date()),Month(Date())+IIf(Day(Date ())<18,0,1),18)

    Just needed the extra brackets after Date in the Iif Function.
    Thanks for your help.

  13. #28
    Join Date
    May 2020
    Posts
    26
    Quote Originally Posted by davegri View Post
    Can you post that DB here, to provide a non-working example?
    HI,
    Cracked it eventually.
    Between DateSerial(Year(Date()),Month(Date())+IIf(Day(Date ())<18,-1,0),18) And DateSerial(Year(Date()),Month(Date())+IIf(Day(Date ())<18,0,1),18)

    Missing the extra brackets after Date in the Iif function.
    Many Thanks for your help

  14. #29
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Don't know why, but Access sometimes removes the () when I include it in the date() function.
    Glad you got it sorted out.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-29-2014, 10:14 AM
  2. Replies: 5
    Last Post: 10-08-2014, 02:23 PM
  3. Replies: 6
    Last Post: 05-05-2014, 10:51 PM
  4. Update Query to change Renew Dates
    By GraemeG in forum Queries
    Replies: 4
    Last Post: 04-07-2011, 12:09 PM
  5. Renew Multiple records via a form
    By Phil Knapton in forum Forms
    Replies: 23
    Last Post: 01-01-2011, 07: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
  •  
Other Forums: Microsoft Office Forums