Results 1 to 3 of 3
  1. #1
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    94

    How to return a number from a character or number table with lookup or query

    Hi Folks, I'm very new to Access so please explain any answer in full for which I thank you in advance.
    I have a field in Table 'Bookings' that contains a lookup to choose one of 5 or 6 choices of cleaning service for property owners to provide to their holidaymaker renters. So the field ends up with a number (which I could change to a character value or to a letter, a, b c etc.).
    Later in the record entry I want to be able to assign a cost to the owner and a payment to the cleaner in two further fields.
    So a lookup or query that is accessed from the data entry field would be ideal. The lookup/query would produce a list with the 'Cleaning' numbers in, and a description of the level of service. Upon clicking a choice, the 'CleanersPay' field and the 'ChargeOwner field in the 'Bookings' table would be filled with the €s amount.
    I've tried with nested IIf function and a lookup, but I think there may be a problem with mixing character with number - DeliaPay: IIf([Limpieza]="1",20,IIf(Limpieza]="2",30,IIf([Limpieza]="3", 40, 10)))
    I hope I have explained adequately what I am looking for clues about.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If Limpieza is a number type field, then don't use quote delimiters. The posted expression shows a missing [ for the second Limpieza.

    DeliaPay is a calculated field in query?

    By 'lookup' do you mean lookup field in table? I NEVER do lookups in table design.

    Saving calculated data into table field will require code - VBA or macro.
    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
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    94
    Thanks June7, I have tried the multiple IIf route with Limpieza as a number, without the delimiters and as a character field and with the "s. I hadn't noticed the missing [ but either way it returnns error:The expression you entered contains an invalid ,(dot) or ! operator or invalid parentheses.
    Regarding the query, I'm really looking for inspiration on what to use and I understand you wouldn't recommend a query, so another lookup table? What I want to do is give the user (my daughter) the ability to have the DeliaPay field filled with a currency or number field representing the cost of the chosen service, numbered in Limpieza field.
    For example, the user gets a booking from owner saying she wants the property cleaning only for the arrival, she chooses this from the Limpieza lookup, returning (say) 1 which could be a character or number. I want the DeliaPay field to be calculated by referring to the Limpieza number (or character) i.e. if Limpieza is 1 then DeliaPay is 25.50. If Limpieza was 2 then DeliaPay could be 35.75 etc.. So what do you think would be the best way to achieve this please? I'm switching from Alpha 5 so I have done some Visual Basic but not too clever with it (I even leave out parentheses!).
    Thanks again,
    Trevor.Click image for larger version. 

Name:	Screenshot (44).jpg 
Views:	7 
Size:	156.1 KB 
ID:	31946

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

Similar Threads

  1. Replies: 15
    Last Post: 12-22-2016, 12:16 PM
  2. Replies: 1
    Last Post: 01-18-2015, 12:01 PM
  3. Replies: 2
    Last Post: 05-21-2014, 06:16 AM
  4. Replies: 2
    Last Post: 08-09-2010, 08:13 AM
  5. Replies: 1
    Last Post: 02-05-2009, 04:53 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