Results 1 to 7 of 7
  1. #1
    Kcgp is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    5

    Question Need help in VBA coding

    Hi Everyone,
    I am new to MS Access and i am in need of a requirement in Access as below.

    I have a table with column "COL1" and column "COL2" with values as below.

    COL1 COL2


    ----------------------
    A 10
    A 20
    A 30
    B 20
    B 30
    C 10
    C 20
    D 20
    D 30

    In here i must read COL1 and on the first occurrance of "A" it must check value in COL2 is 10. If it is not 10 then it must return "FALSE" Message as value in a new field,else it must return "TRUE" as value in a new field..

    Same must occur for B,C,D etc..

    Please help me in writing a VBA code or by means of Query.

    Thanks in advance.

    Regards
    Kcgp

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,114
    Sample data does not show repeat values in Col2 within each group. If this is true for the entire dataset, doesn't matter if it's the first occurrence of A, B, C, D. Just calculate if Col2 is 10, then true, else false. Create a field in query with an expression. The entry on the Field row of query grid would be like:

    IsTen: IIf([Col2]=10, "True", "False")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Kcgp is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    5

    Smile

    Hi june,
    Thanks for your reply. The output must be like

    col1 col2 col3(required output)
    A 10 T
    A 20 T
    A 30 T
    B 20 F
    B 30 F
    C 10 T
    C 20 T
    D 20 F
    D 30 F

    Here i must check if the first occurance of A has "10".If it is having then all the entries of a must have "T", same way for B,C,D. But in case of "B" we can see that for its first occurrance the value is "20". when this happens the col3 must be filled with value "F".

    Please do suggest me how can we do it.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,114
    Okay, try a DLookup function

    IsTen: IIf(DLookup("Col2","tablename","Col1='" & [Col1] & "' AND Col2=10")=10, "T", "F")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Kcgp is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    5
    Hi June,
    I tried what you have given. But it is stating an Data type mismatch error.

    my col1 and col2 are number data type. say like this

    col1 col2 col3
    23 10 T
    23 20 T
    23 30 T
    45 20 F
    45 30 T
    50 10 T
    50 20 T
    60 30 F
    60 40 T

    Please provide me a way to get the above.. :-)

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,114
    Your first data sample showed COL1 as text data, now you show it as numbers. If it is a number datatype then remove the apostrophe delimiters.

    Apostrophes are for text, # for dates, numbers don't need any.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Kcgp is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2012
    Posts
    5
    Thanks June :-)

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

Similar Threads

  1. Email coding
    By arrowmakersuk in forum Access
    Replies: 3
    Last Post: 12-19-2011, 10:06 AM
  2. Date coding
    By shralpy in forum Programming
    Replies: 7
    Last Post: 11-25-2011, 04:29 PM
  3. Access without coding
    By kp123 in forum Access
    Replies: 4
    Last Post: 11-25-2011, 03:50 PM
  4. Coding newbie
    By Phoenyxsgirl in forum Programming
    Replies: 2
    Last Post: 10-13-2011, 08:37 AM
  5. Bar-Coding
    By texasprincess7 in forum Access
    Replies: 1
    Last Post: 02-12-2009, 10:29 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