Results 1 to 12 of 12
  1. #1
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116

    dcount syntax for comparing variable with form field

    Can anyone help me with the syntax in VBA for comparing one variable against a closest like field in tbl_somewhere.Field (text field) ,I know it must be pretty simple to others in the know but to me well I am a still a novice/numpty


    I,ve tried searching and saw lots of uses, but can't quite get it right for my purposes

    I imagine it to be something like the following (close but no cigar):


    Code:
    
    iif DCount=('variableName' Like "me![Feild]") > 0 Then
                 'show record or do something
         else
    msxbox "no like matches found", vbok
    'do something else

    Last edited by gint32; 02-12-2016 at 11:30 AM. Reason: typo

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Here's some examples of the proper syntax for the domain functions. All of the parameters of all of the Doamin functions are *strings*.
    http://access.mvps.org/access/general/gen0018.htm

  3. #3
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116
    thanks some good examples...another question though what is criteria a variable that the users defines or a key word.
    Code:
    For strings: (note the apostrophe before and after the value)
            DLookup("FieldName", "TableName", "Criteria = '" & forms!FormName!ControlName & "'")
    below I've amended to suit my needs, but still no results and it remains red in the vba window:

    Code:
    DLookup("fld_Surname", "tbl_Students", "Criteria = '" & Forms!Calendar_of_attendance!txtUser & "'")

  4. #4
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    suspect the example is using words that have meaning - in this case Criteria. In your code it needs to be the name of the field that will match to txtUser.

    Also, if this code is in your calendar of attendance form the you all need is

    DLookup("fld_Surname", "tbl_Students", "UserName = '" & txtUser & "'")

    With regard the red bit, I susepect you are not assigning it to a value e.g.

    myString=Dlookup(....

  5. #5
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116
    thanks, though I don't want to assign a value I just want it to compare two values on my form for a likeness and tell me if true or false
    such as :

    If DCount("*", "[Students]", "whois Like '*" & Me![scrStudent] & "*'") > 0 Then

    If DLookup("Surname", "Students", "whois = '" & txtUser & "'") > 0 Then

    but of course these examples don't work, cause if they did I won't be asking for help, thanks

    point I don't get is why do I have to have four values in the line of code

    surely, it should be:

    please search and compare (some field) from (sometable) with (somevalue)

    then, tell me not a like match or yes there is a like match

    Does that make any sense?

    Thanks in advance

  6. #6
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116
    There has to be a simple solution to this it can't be that hard to someone who knows a little bit if vba, which I don't I just try and reuse others code.

    simply, I just need to compare two values to see if there is a likeness(not an exact match ) like is mcgintyg like mcginty using what ever is appropiate like dlookup/dcount/islike/compare/isnotlike

  7. #7
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    for this to work

    If DCount("*", "[Students]", "whois Like '*" & Me![scrStudent] & "*'") > 0 Then

    If DLookup("Surname", "Students", "whois = '" & txtUser & "'") > 0 Then
    whois, scrStudent and txtUser need to be strings

    whois is the name of a field in your Students table

    scrStudent and txtUser need to be the names of controls on your form

    DLookup returns a string (Surname) I very much doubt these are called 0, 1, 2 etc so it will never be >0, if there is not a match you would wrap the dlookup in the isNull function or use the nz function

    but of course these examples don't work
    and this is not helpful - what does it mean? you get an error message, the result is not what you expect or what?

    I just need to compare two values to see if there is a likeness(not an exact match ) like is mcgintyg like mcginty using what ever is appropiate like dlookup/dcount/islike/compare/isnotlike
    your dcount should do this - whois=mcgintyg and scrStudent=mcginty

    If you want it the other way round then you would use

    ...., Me![scrStudent] & " like '*' & whois & '*'")

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  9. #9
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116
    Fuzzy Matching Looks great, but for too complicated for my level to implement though.
    Isn't there a way of saying:

    If SomeString is like SomeOtherString then do something , else do something else

    or even better for me

    If SomeString(mcginty) contains within SomeOtherString(mcgintygh) the same string then do something, else do something else

  10. #10
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116
    Fuzzy Matching Looks great, but for too complicated for my level to implement though.
    Isn't there a way of saying:

    If SomeString is like SomeOtherString then do something , else do something else

    or even better for me

    If SomeString(mcginty) contains within SomeOtherString(mcgintygh) the same string then do something, else something else

  11. #11
    gint32 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jan 2015
    Location
    Australia
    Posts
    116

    ...................

    ...........

  12. #12
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    or even better for me

    If SomeString(mcginty) contains within SomeOtherString(mcgintygh) the same string then do something, else something else
    do you read all the posts on your thread or just the one you that has been emailed to you? See post #7

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

Similar Threads

  1. Using DCount, how to use a variable as the criteria
    By crowegreg in forum Programming
    Replies: 2
    Last Post: 02-17-2015, 05:18 PM
  2. Replies: 3
    Last Post: 12-21-2011, 10:49 AM
  3. Trouble with DCount syntax.
    By jessica.ann.meade in forum Database Design
    Replies: 9
    Last Post: 02-18-2011, 10:09 AM
  4. dcount syntax correct?
    By lbgtp in forum Reports
    Replies: 2
    Last Post: 12-29-2010, 10:01 AM
  5. VBA DCount() with variable field name
    By drh in forum Programming
    Replies: 2
    Last Post: 07-13-2010, 12:36 AM

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