Results 1 to 8 of 8
  1. #1
    dwwally is offline Novice
    Windows 10 Access 2007
    Join Date
    Jun 2016
    Posts
    16

    Using record.find with numeric and currency data types

    Two find examples do not work with the syntax I use:

    Example One


    Set rs = Me.Recordset.Clone
    Rem Symbol is a number field
    str = "[SYMBOL] = " & Me![Combo23]


    REM gives argument not optional error
    REM Print "[SYMBOL] = " & Me![Combo23]
    REM [SYMBOL] = 7344


    Set rs = Me.Recordset.Clone
    rs.FindFirst str
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark


    Example Two:




    Rem TRANS_AMOUNT is a currency field
    rs.FindFirst "[TRANS_AMOUNT] = " & Me![Combo25]


    REM gives
    REM Syntax error in expression, Error 3077
    REM Print "[TRANS_AMOUNT] = " & Me![Combo25]
    REM [TRANS_AMOUNT] = $536,181.36


    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

    I have no problem with string fields and dates. Help please

  2. #2
    nrgins's Avatar
    nrgins is offline some guy
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    62
    Are the finds that work and the finds that don't work working with the same recordset? The errors you're getting sounds like the problem's with the recordset itself, not the Find syntax.

    Either way, you can simplify your code a bit:

    Code:
    With Me.Recordsetclone
    	.findfirst "Symbol=" & Me.combo23
    	if not .nomatch then me.bookmark = .bookmark
    end with

  3. #3
    dwwally is offline Novice
    Windows 10 Access 2007
    Join Date
    Jun 2016
    Posts
    16
    Your code for Symbol solved the problem, but is does not seem to work for the TRANS_AMOUNT (currency field)
    I get message:
    Syntax error in expression
    Microsoft Access 3077

    Rem TRANS_AMOUNT is a number field
    With Me.RecordsetClone
    .FindFirst "TRANS_AMOUNT=" & Me.Combo25
    If Not .NoMatch Then Me.Bookmark = .Bookmark
    End With


    Is it possible that the code does not deal with the $ symbol and brackets for negative numbers like ($55.67)
    Rem TRANS_AMOUNT is a number field
    With Me.RecordsetClone
    .FindFirst "TRANS_AMOUNT=" & Me.Combo25
    If Not .NoMatch Then Me.Bookmark = .Bookmark
    End With


    Thanks for your help

  4. #4
    nrgins's Avatar
    nrgins is offline some guy
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    62
    You said Trans_Amount was a currency field. Currency is a numeric data type (not to be confused with the "Currency" text format -- very confusing, I know). Numeric data types can't contain $ or (). If your field is truly a Currency data type, but is formatted to display $ (), then that won't be a problem. If, on the other hand, it's a text data type that is formatted as "Currency," then that would be a problem. Please confirm what the actual data type is in the table itself, not in the form.

    Also, when you get this error, I assume you get a message box with Debug and End buttons, right? When you get the error, click Debug, and tell me which line is highlighted yellow.

    Also, while the line is still highlighted yellow, press Ctrl+G, and then, in the Immediate window that appears, type:

    ? me.combo25

    and press Enter. Tell me what value appears.

  5. #5
    dwwally is offline Novice
    Windows 10 Access 2007
    Join Date
    Jun 2016
    Posts
    16
    Dear nrgins, thanks for your help. In response to your message
    field TRANS_AMOUNT is a currency filed in my table with decimal places = Auto
    The code exits to error on line
    .FindFirst "TRANS_AMOUNT=" & Me.Combo25
    with msgbox err.description "
    Syntax error in expression"
    msgbox err.number 3077

    the ? me.combo25 displays the correct value such as
    ($55.67)

  6. #6
    nrgins's Avatar
    nrgins is offline some guy
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    62
    Actually, no, the combo25 value is not correct. Recall that I previously wrote:

    If your field is truly a Currency data type, but is formatted to display $ (), then that won't be a problem. If, on the other hand, it's a text data type that is formatted as "Currency," then that would be a problem.
    The results in the Immediate window show that you're using a text value with formatting characters in your FindFirst expression, not a Currency value. You need to convert it to a true Currency value using CCur(), not a text value that's formatted as currency. Changing your line of code to this:

    Code:
    .FindFirst "TRANS_AMOUNT=" & CCur(Me.Combo25)
    will fix your problem.

  7. #7
    dwwally is offline Novice
    Windows 10 Access 2007
    Join Date
    Jun 2016
    Posts
    16
    Dear nrgins, thanks a million. THis worked, SOrry I misunderstood your previous note, but in any case did not know the CCur() function

  8. #8
    nrgins's Avatar
    nrgins is offline some guy
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    62
    Great! And I don't know how your data is stored in the table, if it's stored as a Currency data type or not. But, if not, then you should make sure that it is, rather than text.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Several The types of currency
    By azhar2006 in forum Access
    Replies: 7
    Last Post: 07-06-2022, 06:33 AM
  2. Replies: 3
    Last Post: 09-01-2015, 04:36 AM
  3. Replies: 7
    Last Post: 10-25-2011, 08:32 PM
  4. Memo Data Types
    By Joanne Searcy in forum Database Design
    Replies: 4
    Last Post: 12-28-2010, 08:04 PM
  5. Find data, load data, and save as a new record
    By hawzmolly in forum Access
    Replies: 0
    Last Post: 10-05-2008, 03:18 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