Solved Date issues (1 Viewer)

[jazsriel]

Hi,

I have been receiving help from another poster arnelgp, he has been a huge help getting part of this project I am working on cleared up, things that were far beyond my skillset. However I am running into another issue or 2.

I am uploading a copy of the database I am working on. So here is the problem(s), on query 1, I have added 3 expressions to format the Date in a specific way, expr1 shows just the month, expr2 shows just the year and expr3 shows just the day. However, once I put these controls onto Report1 then it gives me the following error: "The number of columns in the two selected tables or queries of a union query do not match." I have no clue how to clear this up, this is beyond my knowledge.

The only other problem I will have after this will be to make sure this report has the months reported correctly. What I mean is currently we are in December 2020, when Jan 2021 gets here I need the report to show the Jan 2021 data as a new report instead of being a continuation of Dec 2020.

Anyway I hope someone can help me with this.

Thank you.

 

[moke123]

You have the following union query in your report module

strSQL = strSQL & "select * from Query1 "
    strSQL = strSQL & "Union "
    strSQL = strSQL & "select Top " & intPad & " * from dummy;"

They apparently dont have the same number of fields (columns).

With a union query if the first query must have the matching number of fields as the second query. If need be you can always add a dummy column to one of the queries.

 

[jazsriel]

I have tried to add dummy columns to the qryDateCounts query to ensure it has the same number of fields, but it is still giving me the same error.

 

[The_Doc_Man]

The union sub-queries have to match in both number of fields and data type, I believe.

 

[plog]

You need to fix your table before proceeding.

1. 'Date' is a reserved word and shouldn't be used for a field name. Instead you should prefix it with what that data represents (InventoryDate, SalesDate, etc.)

2. For numeric data, you should store it as a numeric field type. NoUnits, Loss, and TotalLoss should not be Short Text.

3. Use better names all around. You've just accepted the generic prompts from Access for your object names--Table1, Query1, Form1, Report1.

 

[jazsriel]

Plog, I appreciate that you took time to reply, however, your post has nothing to do with my issue.

 

[arnelgp]

here i revisited your db again.
i added the 3 new fields in dummy table.
i added another criteria to Query1 to show only "Current month" (you already have the "current year").

 

[jazsriel]

arnelgp, this project/problem has been solved. You are the best. Thank you for your help and time with this. I had no clue how to do any of this. Thank you so much.

 

[arnelgp]

you're welcome.
again i check your Query1, it seems you don't need the 3 additional columns.
you can set them on the report (see the Control Source of each "Expr" textbox).

 

[jazsriel]

Hi again, I see what you did with it and it looks really good. I have been inputting information to play around with it and I am confused about something. I put items in for dates back in November 2020 and also items for next month Jan 2021 just top see if the items show up as separate reports, and those items will show up in Table 1 as appropriate, but they do not show up in Query1, or on Form1 after you close and reopen it, and definately does not show up on Report1. I am not understanding why it is not showing up. Any ideas?

 

[arnelgp]

see Query1 in design view.
it has a Criteria to only show records for This month, and This Year.

 

[jazsriel]

I removed those and it shows all dates and days as you have stated. However I am trying to get the new months to show on separate pages, anyway to do that? I have been playing with grouping but seems to not work as I need.

 

[HealthyB1]

Try this. I haven't put them on separate pages but they are in separate sections so you should be able to proceed from here

 

[jazsriel]

Hi, I see what you did with the grouping and adding the date header. I have tried to incorporate them onto separate pages as I am needed, however the other requirement I have for it to have the rest of the pages blank areas be filled with boxes ends up failing. I do thank you for the attempt and help though.

 

[arnelgp]

see this.

 

[jazsriel]

I saw that, and with some grouping I was able to put them on new pages, but then the first problem I originally had where I needed those blocks to fill up the blank pages no longer works. But at the same time, having the dates visible before each section is also not what is needed. Is there is a way to start a new report with each month with keeping all the other aspects that are working in place? I am not able to see a solution to this.

 

[arnelgp]

did you really saw it, have you downloaded it and tried? my upload is different from HealthyB1.

 

[jazsriel]

I did download and checked the report on the one you uploaded. It is showing the date at the top of the report of each section, I went ahead and separated them into separate reports and it no longer shows the blocks in the empty space, as I needed originally. The one the previous poster put up was doing the same thing. I have 2 screens on my computer, and I have both DBs open right now. I am not seeing any difference as I compare the two of them. I have looked over all the queries, and tables. I am not seeing anything that is different.

 

[arnelgp]

if you download it, then you did not noticed that Each month is in separate page?

 

[jazsriel]

I did download it, and I swear they were on the same page, just had the Date headers above them just like on the other posters attempt they uploaded. At least I thought I did, maybe I didn't overwrite theirs since it was the same name, let me retry.