Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Location
    sweden
    Posts
    3

    Post Unanswered: negative values?

    I have created a simple database in Access 97 containing carbondated samples. The data comes in years BC and AD. How can I make the fielda searchable? Ive tried entering the BC-Years as negative numbers, but Im getting problems in searching intervals (ex: 500 BC - 1000 AD = from -500 to 1000). The terms <= and >= doesnt work obviously.Very new at this....

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Try using "between" instead of >= or <=

    Like: Between -500 and 1000
    Inspiration Through Fermentation

  3. #3
    Join Date
    Aug 2003
    Location
    sweden
    Posts
    3
    Thanks! It seems to work fine! Is there a way to make the database convert a searchstring, for example if a user is prompted to type a "max age" and "min age" including BC (negative value) or AD (Positive value). That is if you type 1000 BC the database returns the -1000 value and vice versa with AD?

    Between [type minimum age BC/AD:] and [type maximum age BC/AD:]

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I can't think of a way to do it without using VBA.
    Maybe one of the guru's here can offer something that will do it with a single formula.

    If no one else replies, I'll get back to you in a few hours with the code you need.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Try this code behind a button to open the report:
    (It could be shortened, and have some more error trapping added...)

    Function Convert_DateRange()
    Dim strInput As String, strBCorAD As String * 2
    Dim lngYear_beg As Long, lngYear_end As Long
    'get the beginning year
    strInput = InputBox("Type minimum age BC/AD", "Starting Year")
    strBCorAD = Right(strInput, 2)
    'make sure the user put in a valid number at the beginning of the string
    If Not IsNumeric(Trim(Left(strInput, Len(strInput) - 2))) Then
    GoTo BADINPUT
    End If
    Select Case strBCorAD
    Case "AD"
    lngYear_beg = CLng(Trim(Left(strInput, Len(strInput) - 2)))
    Case "BC"
    lngYear_beg = CLng(Trim(Left(strInput, Len(strInput) - 2))) * -1
    Case Else
    'the user didn't input BC or AD at the end
    GoTo BADINPUT
    End Select
    strInput = InputBox("Type maximum age BC/AD", "Ending Year")
    strBCorAD = Right(strInput, 2)
    'make sure the user put in a valid number at the beginning of the string
    If Not IsNumeric(Trim(Left(strInput, Len(strInput) - 2))) Then
    GoTo BADINPUT
    End If
    Select Case strBCorAD
    Case "AD"
    lngYear_end = CLng(Trim(Left(strInput, Len(strInput) - 2)))
    Case "BC"
    lngYear_end = CLng(Trim(Left(strInput, Len(strInput) - 2))) * -1
    Case Else
    'the user didn't input BC or AD at the end
    GoTo BADINPUT
    End Select
    'insert the name of your report, and the field name containg the data that contains the range
    DoCmd.OpenReport "YourReport", acViewNormal, , "[Yourdatefield] between " & lngYear_beg & " and " & lngYear_end
    Exit Function
    BADINPUT:
    MsgBox "Invalid Date Input"
    Exit Function
    End Function
    Inspiration Through Fermentation

  6. #6
    Join Date
    Aug 2003
    Location
    sweden
    Posts
    3

    Smile

    Thanx a bunch! It works great!

Posting Permissions

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