Results 1 to 13 of 13
  1. #1
    jmichaels is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    20

    Use of Numeric Constant in Query


    I have an Access database that has 12 individual queries in which one of the fields is "Month Ending". I set that field to a particular month in our current fiscal year. Next year I will have to go back and change the field to reflect the month ending for the next year. Is there a way to use a constant to update the queries instead of editing each individual query?

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    monthname(month(date)) will give you "October"


    month(date) will give you 10

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You could always make 1 query, that uses the dates on a form.
    pick the start/end dates.

    Or even pick the month, and the form fills the start/end dates.

  4. #4
    jmichaels is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    20
    Thanks for the reply! But, I'm actually setting the Month_Ending field value to a particular month so that every record returned in that query has the same month ending value. Each query is for a different month.

  5. #5
    jmichaels is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    20
    Maybe some clarification is needed...I have a table that has monthly forecast numbers for a given product. The table is in crosstab format. So, the fields are Product, Period1, Period2, Period3, Period4, etc... where the Period1 field holds the forecast value for our first fiscal month, Period2 holds the value for our second fiscal month, etc... I'm trying to create a table which is essentially Product, Month_Ending, Amount. So, each query is for a given month. In the first query, I set Period1 to equal 1/31/2020 and the amount is pulled from the Period1 field of my original table. I then use a union all query to combine the 12 queries. I'm just trying to avoid the need to go into each individual query to change the values to which Month_Ending is set.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the table is in crosstab format.
    sounds like this is your problem, tables do not have a crosstab format, you use a query. So the implication is you have a spreadsheet structure to your table. And these do not work in databases.

    Your table should look something like

    tblForecasts
    ForecastPK
    ProductFK
    FiscalYear
    FiscalPeriod
    Forecast

    you would then use a crosstab query to present this 'horizontally'.

    So only one query required.

  7. #7
    jmichaels is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    20
    Unfortunately, I had to set up my table in a crosstab format because the data that I copy and paste in is in a crosstab format.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You don't need to have the tables in a non-normalized structure just because the data you get is in a "spreadsheet" format. Easy enough to have code to convert the "spreadsheet" format to a normalized format.
    As Ajax stated, you would only need 1 query instead of 12 queries.
    Maybe you would post your dB?

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    I agree wholeheartedly with all of the responses above.
    However if you insist on doing it your way, look at setting a TempVar and use that in your query(ies) instead.

    However a crosstab query is easy enough to create as and when needed.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    because the data that I copy and paste in is in a crosstab format.
    So to save you spending a few minutes writing something once to convert your data to a normalised format each time you import, you are happy to spend hours having to find ways to work around your denormalised crosstab structure?

  11. #11
    jmichaels is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    20
    @Ajax, I definitely want the easiest solution. It's only 100-200 lines in a spreadsheet. My process has been to run the report, copy and paste append it in to the table. I've never really thought about fixing the format before the data is saved rather than after. It makes complete sense. It's just that I know how to do basic queries but have little experience with VBA. So, can you point me to some example code that does this?

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Some sample source data and how/when it is generated would allow for more focused help. In other words, how dynamic/fluid is the data? Is it a worksheet that is replaced periodically? If so, does it get a new name? Or the same questions but for a workbook. The answers may reveal that you could start by linking the spreadsheet to Access as if it were a table, then use a couple of queries to populate the required Access table(s) every period. There are also other methods such as TransferSpreadsheet, or automation (more complicated) or using an import specification. All depends on what you have. That is all related to stage 1. If there is a stage 2 it will likely be massaging the data so that you can do what you need reliably and without a lot of unnecessary work. What you're doing now is not only a lot of unnecessary work, it's untenable.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    Join Date
    Apr 2017
    Posts
    1,673
    You can calculate last date of any month as day number 0 of next month, like to get 30. september of current year
    Code:
    DATESERIAL(YEAR(Date()), 10, 0)

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

Similar Threads

  1. Replies: 9
    Last Post: 01-16-2019, 02:13 PM
  2. Replies: 8
    Last Post: 09-12-2015, 11:28 AM
  3. Replies: 2
    Last Post: 01-31-2015, 09:29 PM
  4. Replies: 4
    Last Post: 04-01-2014, 03:27 PM
  5. Replies: 1
    Last Post: 06-09-2010, 04:19 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