Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2004
    Posts
    287

    Unanswered: For i statement.. problem

    I am attempting to run the following code via a For i statement as below;

    Code:
    Dim daybox As String
    Dim dayday As String
    Dim i As Integer
    For i = 1 To 31
         daybox = "[" & "Jan" & CStr(i) & "]"  ' [Day1], [Day2]
         dayday = "#01/" & CStr(i) & "/2011#" ' #01/xx/2011#
         
    daybox = DLookup("[DayType]", "Qry_MainSelectStaff", "[DayDate] = " & dayday)
    Next i
    I have ran this type of for i statement before without any problems, but I can't seem to work around this one.

    I need to basically run the following line 31 times, for each day;
    [Jan1] = DLookup("[DayType]", "Qry_MainSelectStaff", "[DayDate] = #01/01/2011#")

    SO the above statement will do this for me without typing it 31 times.

    Any suggestions. It appears to be erroring on the syntax for [dayday] for the [DayDate] field. I have tried various other forms of this code but to no avail so thought it best to head to the forums for some friendly advice
    Last edited by NeilMansell; 10-26-11 at 12:48.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    An error can happen if there is no match for the criteria. In such a case DLookUp will return a Null value that you cannot assign to daybox which is a string. What's the error (code and message) returned?
    Have a nice day!

  3. #3
    Join Date
    Mar 2004
    Posts
    287
    Error code '94'
    Invalid use of null - which I assume is the dayday not being read as a date format.

  4. #4
    Join Date
    Mar 2004
    Posts
    287
    this code returns invlid use of null - i believe all i have to do is create the # (hashes) before the date format

    Code:
    Dim daybox As String
    Dim dayday As Date
    Dim i As Integer
    For i = 1 To 31
    
    'January
         daybox = "[" & "Jan" & CStr(i) & "]"  '[Jan1], [Jan2]
         dayday = "01/" & CStr(i) & "/2011"  '#01/xx/2011# 
    
    Debug.Print dayday
    daybox = DLookup("[DayType]", "Qry_MainSelectStaff", "[DayDate] = " & dayday)
    but I still can't get the correct syntax. debugging the dayday - it reads 01/01/2001
    so that part is working ok.

    (I have also tried it without the hashes on the date (dayday))

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No, it means that DLookUp returns a Null value. You can test it by declaring daybox as Variant: the error will not occur and if you test the value of daybox after calling DLookUp, you'll see it's Null.
    Have a nice day!

  6. #6
    Join Date
    Mar 2004
    Posts
    287
    i will chedk but just to add I have added this;

    Code:
         daydaycomplete = "#" & dayday & "#"
    
    daybox = DLookup("[DayType]", "Qry_MainSelectStaff", "[DayDate] = " & daydaycomplete)
    And the debug window shows that
    dayday = 01/01/2011
    daydaycomplete = #01/01/2011#


  7. #7
    Join Date
    Mar 2004
    Posts
    287
    any suggestions please? I'm stuck now having tried everything I know and have read up on.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    instead of:-
    Code:
    daydaycomplete = "#" & dayday & "#"
    daybox = DLookup("[DayType]", "Qry_MainSelectStaff", "[DayDate] = " & daydaycomplete)
    try

    Code:
    dim daydaycomplete  as date
    daydaycomplete = cdate(dayday)
    daybox = DLookup("[DayType]", "Qry_MainSelectStaff", "[DayDate] = " & daydaycomplete)
    the reason
    # should be used for date literals
    I don't know if appending the # to a string representation of a date works, but forcing your value into a date variable should do the job

    just a comment I think you will benefit from giving your variables a clear concise descriptive name.. I haven't got a scooby what dayday and daydaycomplete are, you probably do, but you may not as and when you get down to revisiting this code in the future
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2004
    Posts
    287
    didn't work I'm afraid, i get an invalid use of null error on:

    daybox = DLookup("[DayType]", "Qry_MainSelectStaff", "[DayDate] = " & daydaycomplete)

    I also have comments regarding the fields names above the code I just didn't post them on here.
    Last edited by NeilMansell; 10-27-11 at 07:07.

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The problem does not come from the way you assemble the value of daydaycomplete, it works. However, you cannot assign a Null value to a string. Try:
    Code:
    daybox = Nz(DLookup("[DayType]", "Qry_MainSelectStaff", "[DayDate] = " & daydaycomplete),"")
    Have a nice day!

  11. #11
    Join Date
    Mar 2004
    Posts
    287
    thanks. debugging it I have found that the date field is incorrect (US/UK);

    dayday = 01/30/2011
    daydaycomplete = 30/01/2011

    Is there a way to make it US style for daydaycomplete

    Other than that it is no longer erroring

  12. #12
    Join Date
    Mar 2004
    Posts
    287
    i believe it is due to rhe formatting of the daydaycomplete field.

    I have tried the following;
    daydaycomplete = (dayday)
    daydaycomplete = Format(daydaycomplete, "mm/dd/yyyy")

    but can't seem to work out the correct syntax to convert the daydaycomplete date format to the us style so I can insert the code appropriately.

    Any suggestions?

  13. #13
    Join Date
    Mar 2004
    Posts
    287
    update:

    Code:
         daybox = "[" & "Jan" & CStr(i) & "]"  '[Jan1], [Jan2]
         dayday = "01/" & CStr(i) & "/2011"  '#01/xx/2011#  >>> MONTH FIRST mm/dd/yyyy
    
    Debug.Print daybox
    Debug.Print dayday
    daydaycomplete = (dayday)
    daydaycomplete2 = Format(daydaycomplete, "mm/dd/yyyy")
    Debug.Print daydaycomplete2
    
    daybox = Nz(DLookup("[DayType]", "Qry_MainSelectStaff", "[DayDate] = #" & daydaycomplete2 & "#"), "")
    Debug.Print daydaycomplete2
    Debug Print shows the following;

    daybox = 01/31/2011
    dayday = 31/01/2011
    daydaycomplete2 = 31/01/2011

    So it needs to converted the date, (although it doesn't error now).
    Am I missing something to convert daydaycomplete to mm/dd/yyyy ?
    Last edited by NeilMansell; 10-27-11 at 09:42.

  14. #14
    Join Date
    Mar 2004
    Posts
    287
    anyone please?

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    cdate use the date format of the international locale of where you are
    so it understands that 10/05/2011 is the 10th of May, not the 5th of October, assuming your internationalisation settings are correctly.

    without knowing what datatype daydaycomplete is its hard to knwo whether the format will work as anticipated.

    persoanlly I think youneed to be much more explicit about dates. Access & VBA does a good job of interpreting what it thinks youmean, but its far better to be specific.

    we don't know where daydaycomplete is coming from.. is it user input, a calendar control, a column or where.....
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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