Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

#error in a query

Discussion in 'Information Technology' started by Amie, Jul 28, 2006.

Tags:
  1. Amie

    Amie
    Expand Collapse
    Guest

    I have been searching the internet to help solve what seems to be a
    relatively common error. I created an Access module to calculate
    Networking days between two dates. Then I set up a query so that I
    could use the module/code however if one of my date fields is blank I
    get a #error message in the query. Is there a way to change the #error
    message to be a 0 or N/A? I saw this question posted but I couldn't
    find the response. BTW - I am using MS Access 2002

    Any help would be greatly appreciated!
     
  2. Loading...

    Similar Threads Forum Date
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006
    Sikhism a query Book Reviews & Editorials Aug 2, 2005

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    What's the code in your existing file?

    I'm guessing you've declared the parameters as Dates. Since Date fields
    can't accept Null values (only Variants can), you'd have to change the
    declaration and introduce logic in the function to handle Null values.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Amie" <amiem8@yahoo.com> wrote in message
    news:1147727151.572228.128160@g10g2000cwb.googlegroups.com...
    >I have been searching the internet to help solve what seems to be a
    > relatively common error. I created an Access module to calculate
    > Networking days between two dates. Then I set up a query so that I
    > could use the module/code however if one of my date fields is blank I
    > get a #error message in the query. Is there a way to change the #error
    > message to be a 0 or N/A? I saw this question posted but I couldn't
    > find the response. BTW - I am using MS Access 2002
    >
    > Any help would be greatly appreciated!
    >
     
  4. Amie

    Amie
    Expand Collapse
    Guest

    They are date fields. Here is my current code for the NetWorking days:

    Public Function WorkingDays(StartDate As Date, EndDate As Date) As
    Integer

    On Error GoTo Err_WorkingDays

    Dim intCount As Integer

    StartDate = StartDate
    'If you want to count the day of StartDate as the 1st day
    'Comment out the line above

    intCount = 0
    Do While StartDate <= EndDate
    'Make the above < and not <= to not count the EndDate

    Select Case Weekday(StartDate)
    Case Is = 1, 7
    intCount = intCount
    Case Is = 2, 3, 4, 5, 6
    intCount = intCount + 1
    End Select
    StartDate = StartDate + 1
    Loop
    WorkingDays = intCount

    Exit_WorkingDays:
    Exit Function

    Err_WorkingDays:
    Select Case Err

    Case Else
    MsgBox Err.Description
    Resume Exit_WorkingDays
    End Select

    End Function

    Would I add logic into the code at this level or in the query level?
    What logic would I add I tried to add an IIF statement but it didn't
    seem to work. I am new at this.

    Thank you for your super quick response.
     
  5. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Hi Arnie,

    It looks like you are using Arvin's sample taken from here:
    http://www.mvps.org/access/datetime/date0006.htm

    Try the following modification. Note the use of (StartDate As Variant,
    EndDate As Variant), which allows us to test for nulls:


    Option Compare Database
    Option Explicit

    Public Function WorkingDays(StartDate As Variant, EndDate As Variant) As
    Integer
    On Error GoTo ProcError

    '....................................................................
    ' Name: WorkingDays http://www.mvps.org/access/datetime/date0006.htm
    ' Inputs: StartDate As Date
    ' EndDate As Date
    ' Returns: Integer
    ' Author: Arvin Meyer
    ' Date: February 19, 1997
    ' Comment: Accepts two dates and returns the number of weekdays between them
    ' Note that this function does not account for holidays.
    '....................................................................

    Dim intCount As Integer

    If IsNull(StartDate) Or IsNull(EndDate) Then
    WorkingDays = 0
    Else
    StartDate = StartDate + 1
    'If you want to count the day of StartDate as the 1st day
    'Comment out the line above

    intCount = 0

    Do While StartDate <= EndDate
    'Make the above < to not count the EndDate
    Select Case Weekday(StartDate)
    Case Is = 1, 7
    intCount = intCount
    Case Is = 2, 3, 4, 5, 6
    intCount = intCount + 1
    End Select
    StartDate = StartDate + 1
    Loop

    WorkingDays = intCount
    End If

    ExitProc:
    Exit Function
    ProcError:
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
    vbCritical, "Error in WorkingDays event procedure..."
    Resume ExitProc

    End Function



    Tom Wickerath, Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "Amie" wrote:

    > They are date fields. Here is my current code for the NetWorking days:
    >
    > Public Function WorkingDays(StartDate As Date, EndDate As Date) As
    > Integer
    >
    > On Error GoTo Err_WorkingDays
    >
    > Dim intCount As Integer
    >
    > StartDate = StartDate
    > 'If you want to count the day of StartDate as the 1st day
    > 'Comment out the line above
    >
    > intCount = 0
    > Do While StartDate <= EndDate
    > 'Make the above < and not <= to not count the EndDate
    >
    > Select Case Weekday(StartDate)
    > Case Is = 1, 7
    > intCount = intCount
    > Case Is = 2, 3, 4, 5, 6
    > intCount = intCount + 1
    > End Select
    > StartDate = StartDate + 1
    > Loop
    > WorkingDays = intCount
    >
    > Exit_WorkingDays:
    > Exit Function
    >
    > Err_WorkingDays:
    > Select Case Err
    >
    > Case Else
    > MsgBox Err.Description
    > Resume Exit_WorkingDays
    > End Select
    >
    > End Function
    >
    > Would I add logic into the code at this level or in the query level?
    > What logic would I add I tried to add an IIF statement but it didn't
    > seem to work. I am new at this.
    >
    > Thank you for your super quick response.
     
  6. Amie

    Amie
    Expand Collapse
    Guest

    Thank you for the help, It worked!! Thank you, thank you, thank you!!!
     
  7. Amie

    Amie
    Expand Collapse
    Guest

    Thank you for the help, It worked!! Thank you, thank you, thank you!!!
     
  8. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

Share This Page