Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    Unanswered: HELP: Microsoft Access can't find the 'date' Field reffered to in your expersion

    i'm geting the above error on the following piece of code
    Code:
    Private Sub Form_Open(Cancel As Integer)
        Dim rs As DAO.Recordset
         If datetest.Value < date - 1 Then
            Set rs = CurrentDb.OpenRecordset("Overdue", dbOpenSnapshot, dbReadOnly)
            DoCmd.RunSQL ("UPDATE OverdueTest SET OverdueTest.[datetest] = Date();")
            While Not rs.EOF
                DoCmd.SendObject , , , rs("firstname") & "." & rs("surname") & "@uk.agimedia.com", _
    , , "job overdue: " & rs("DecriptionBrief"), rs("DescriptionFull"), True, ""
                rs.MoveNext
            Wend
        End If
    End Sub
    the quetion is how the hell do i convince VB that date is the function Date()
    oh and heres the base sql for overdue
    SELECT Supportstaff.firstName, Supportstaff.Surname, Jobs.DescriptionBrief, Jobs.DescriptionFull, Jobs.PromisedDate
    FROM States INNER JOIN (Supportstaff INNER JOIN Jobs ON Supportstaff.ID=Jobs.assignedTo) ON States.id=Jobs.Status
    WHERE (Jobs.PromisedDate<Date() AND Jobs.Status<>3);
    Last edited by m.timoney; 01-14-03 at 12:38.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  2. #2
    Join Date
    Nov 2002
    Posts
    150

    Re: HELP: Microsoft Access can't find the 'date' Field reffered to in your expersion

    Weird, works for me....

    Public Function Test()
    Dim s As Date
    s = #1/2/2002#
    If s < Date - 1 Then
    Stop
    End If
    End Function


    Notice how my Date is in caps and yours isn't? Wonder if you have a VBA dll problem.

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    mines not in caps because VB's auto corrector keeps removing it as well as the brackets, and i've used the date method in other places with no problems the only thing i can think of is that before i renamed it 'datetest' was called 'date', hence the reason i changed it
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    my Alison Balter (OK, it's A97) tells me to use Date() in queries to avoid confusion with fields called "date"...even if you have no such field.

    izy
    Last edited by izyrider; 01-14-03 at 14:04.

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i type 'Date()' access Automaticly changes it to 'date'
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    aha! if my reading skills were better i would have seen that from your first post - sorry.

    do you have a function in scope called "date"?

    what occurs if you use Now() instead of Date() ?

    izy

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    thx, god knows why date don't work because like i said the only date was a txtbox on the form and the field in the txtbox both of which have had there names changed.

    but now work so i'll use that
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    good it works, but meanwhile it looks like you've got something called "date" still in scope.

    maybe Edit¦Find (match case) "date"
    ...but you'll get a zillion hits since you are messing with dates anyway

    izy

  9. #9
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    0 matches 'date' with match case and whole word in entire project
    like i said i can only assume that it's looking at variables that have since been rename and thats why it erroring as :Microsoft Access can't find the 'date' Field reffered to in your expersion
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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