Results 1 to 4 of 4
  1. #1
    qphan is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    2

    MS Access query

    I have an MS Access query and try to understand so I can convert it to SQL query.


    Can someone help me out. Thank you.
    UPDATE AppReferrals SET AppReferrals.DeviceID = CLng(Val(Left(IIf([AppReferrals].[DeviceID] Like '0',[AppReferrals].[ActionID],[AppReferrals].[DeviceID]),9)));

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,126
    It doesn't make complete sense to me. If you're looking for an interpretation:
    If deviceID like 0 then actionID else deviceID, then take the leftmost 9 characters of that expression and apply the Val function to it. Val will stop when it sees a character it cannot interpret as a number. Then coerce what is returned from that string (which looks like a number but must be a string) to a Long data type. Then update appreferrals.devicdID to that value.

    The parts that don't make sense to me: LIKE operator is usually used with wildcards.
    In your case I can't see how anything but '0' can be LIKE '0' but "012" is like "0*" or it is like "*0*"
    Also, if the IIF function returns anything less than 9 characters, you will probably raise an error.
    Not sure if this is desired or not, but if you expect this to only update 1 record, it will update every record where the expression evaluates to True because there is no criteria. So a lot of what's expected seems to rely on the existence of certain conditions. Kinda dangerous sometimes.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  3. #3
    qphan is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    2
    I found the issue. The DeviceID field is nvarchar so the 0 actually can treat as string and use in IIF.
    The left 9 is trim the rest if more than 9 character or number,
    Thank you for your help.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,126
    I found the issue
    Well that's interesting. You didn't say you had an issue, you only said it was an Access query which as I guess you noted, doesn't handle nvarchar.

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

Similar Threads

  1. Replies: 8
    Last Post: 01-11-2020, 04:31 AM
  2. Replies: 3
    Last Post: 07-08-2019, 04:23 PM
  3. Replies: 2
    Last Post: 03-26-2019, 12:40 PM
  4. Replies: 2
    Last Post: 10-07-2015, 12:28 PM
  5. Replies: 3
    Last Post: 04-24-2012, 08:32 AM

Tags for this Thread

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 - Senior Forums