Results 1 to 13 of 13
  1. #1
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116

    Query problem

    I've stuck on a problem, and I'd be very grateful if someone could help me please.



    I'm using Access 2007, and I've created a database for an orchestra. I've created a query with a group by player ID and engagement ID, and entered the total fee (for each player/engagement) as a sum. That all works well. However, I want another column to calculate the VAT on that total fee, if the player has a VAT number. I've tried entering the following - Expr1: IIf([T_MusiciansDetails]![VAT_Number]<>"",[NetTotal]*0.2,"") - both as "grouped by" and "expression". Neither worked. Grouped by splits all the payment types for the VAT players. If I enter it as an expression I get a pop up error 'You tried to execute a query that does not include the specified expression 'IIF(Not [T_MusiciansDetails]![VAT_Number] ="",[T_Payments]![Amount]*0.2 ,"")'. What am I doing wrong? Many thanks for your help.

    Maybe I should add, if I do not use an IIF function, and just enter
    [NetTotal]*0.2 as an expression that works fine, or even IIF([NetTotal]>0,[NetTotal]*0.2,"") also works fine, but obviously I don't want to calculate VAT for non VAT registered players. I'm guessing the problem might relate to me referencing the MusiciansDetails table, but I'm unable to solve it.

    Many thanks.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps you need to add your T_MusiciansDetails table to the query
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    I'm quite new to Access. When you say bring MusiciansDetails to the query, does that simply involve left clicking and dragging it into the query window? I've tried that, but it gets the same error. Should I had done something else? Thanks.

  4. #4
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Since my last message, I seem to be making some progress now, and I've got it to work. Many thanks for your help.

    The next thing, I notice that the VAT column is not being displayed as a currency. But when I add the net total to the VAT, the gross total is displayed as a currency. How can I display the VAT as a currency? Thanks again.

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    If you haven't set your table relationships then you will need to join the two tables. See the link for a video on how to do this: https://www.google.com/search?q=acce...31fAP5vOo0AM40
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by neilsolaris View Post
    Since my last message, I seem to be making some progress now, and I've got it to work. Many thanks for your help.

    The next thing, I notice that the VAT column is not being displayed as a currency. But when I add the net total to the VAT, the gross total is displayed as a currency. How can I display the VAT as a currency? Thanks again.
    You could use CCur() function or set its format property to Currency. I wouldn't bother with either. Since I wouldn't allow users any direct use of tables or queries, I would format the control on any form or report that might use this field.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Many thanks Bob. Whilst I did set table relationships, it's entirely possible I didn't do it correctly, or fully. I'll watch that video you sent me in a bit, and check I've done it properly. The way I got it to work though, was to bring the T_MusiciansDetails table into the query as you suggested, but I then entered the VAT number into the query, and referenced that field using the IIF function, to generate the VAT amount.

    Thanks for the advice about the CCur() function. That's interesting to know. Maybe I'll leave it as it is then.

    My next task if to create self/billing or remittance advice PDF files for each player/engagement, using the data from this query. I managed to automate this before using excel and VBA, but I'd like to do it using Access now. Can Access reports be used to automate this process, or should I be looking to use VBA? Many thanks.

  8. #8
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by neilsolaris View Post
    Many thanks Bob. Whilst I did set table relationships, it's entirely possible I didn't do it correctly, or fully. I'll watch that video you sent me in a bit, and check I've done it properly. The way I got it to work though, was to bring the T_MusiciansDetails table into the query as you suggested, but I then entered the VAT number into the query, and referenced that field using the IIF function, to generate the VAT amount.

    Thanks for the advice about the CCur() function. That's interesting to know. Maybe I'll leave it as it is then.

    My next task if to create self/billing or remittance advice PDF files for each player/engagement, using the data from this query. I managed to automate this before using excel and VBA, but I'd like to do it using Access now. Can Access reports be used to automate this process, or should I be looking to use VBA? Many thanks.
    I don't think I'll be able to offer much help with that endeavour but I'm sure that a Google search or a search of this forum will return some useful information.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  9. #9
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Your help was very valuable so far. Many thanks.

  10. #10
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by neilsolaris View Post
    Your help was very valuable so far. Many thanks.
    Thank you for your kind words. Always pleased to help, if I can

    Incidentally, I wondered if the following link might help in your next endeavour: https://www.datanumen.com/blogs/expo...-file-via-vba/
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  11. #11
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    That's perfect, thanks!
    Last edited by neilsolaris; 05-23-2020 at 05:34 AM. Reason: Misspelling

  12. #12
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by neilsolaris View Post
    My next task if to create self/billing or remittance advice PDF files for each player/engagement, using the data from this query. I managed to automate this before using excel and VBA, but I'd like to do it using Access now. Can Access reports be used to automate this process, or should I be looking to use VBA?
    You can design a report based on your query. Then you can install e.g. CutePDF Writer (it's freeware) as a printer on your computer, and use it for report printout. You are asked for folder and file name, and PDF file with this name is created in target folder. I think it may be possible to design a code which cycles through addressates, calls report with parameters for every of them, and sends printouts to CutePdf Writer too, but I haven't ever searched for calling it through code - so you have to google for it yourself.

  13. #13
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Quote Originally Posted by ArviLaanemets View Post
    You can design a report based on your query. Then you can install e.g. CutePDF Writer (it's freeware) as a printer on your computer, and use it for report printout. You are asked for folder and file name, and PDF file with this name is created in target folder. I think it may be possible to design a code which cycles through addressates, calls report with parameters for every of them, and sends printouts to CutePdf Writer too, but I haven't ever searched for calling it through code - so you have to google for it yourself.
    Thanks for the tip, I'll look into that.

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

Similar Threads

  1. Update Query Problem (Not an Updateable Query)
    By McArthurGDM in forum Queries
    Replies: 6
    Last Post: 02-19-2015, 11:25 AM
  2. SQL query problem
    By jinz in forum Queries
    Replies: 1
    Last Post: 12-05-2012, 04:49 PM
  3. VBA problem in query
    By geniass in forum Queries
    Replies: 41
    Last Post: 08-28-2010, 03:07 PM
  4. query problem i have a problem wi
    By maxx3 in forum Queries
    Replies: 0
    Last Post: 06-29-2009, 02:29 AM
  5. Problem with query
    By maxx3 in forum Queries
    Replies: 8
    Last Post: 06-08-2009, 11:58 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