Solved Date issues (1 Viewer)

jazsriel

Member
Local time
Today, 08:04
Joined
Dec 21, 2020
Messages
45
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.
 

Attachments

  • DB.zip
    101.9 KB · Views: 20

moke123

AWF VIP
Local time
Today, 09:04
Joined
Jan 11, 2013
Messages
2,237
You have the following union query in your report module
Code:
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.
 
Last edited:

jazsriel

Member
Local time
Today, 08:04
Joined
Dec 21, 2020
Messages
45
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

Immoderate Moderator
Staff member
Local time
Today, 08:04
Joined
Feb 28, 2001
Messages
19,045
The union sub-queries have to match in both number of fields and data type, I believe.
 

plog

Banishment Pending
Local time
Today, 08:04
Joined
May 11, 2011
Messages
10,298
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

Member
Local time
Today, 08:04
Joined
Dec 21, 2020
Messages
45
Plog, I appreciate that you took time to reply, however, your post has nothing to do with my issue.
 

arnelgp

error reading drive A:
Local time
Today, 22:04
Joined
May 7, 2009
Messages
11,514
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").
 

Attachments

  • DB (2).zip
    105.6 KB · Views: 14

jazsriel

Member
Local time
Today, 08:04
Joined
Dec 21, 2020
Messages
45
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

error reading drive A:
Local time
Today, 22:04
Joined
May 7, 2009
Messages
11,514
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).
 

Attachments

  • DB (2).zip
    38.1 KB · Views: 19

jazsriel

Member
Local time
Today, 08:04
Joined
Dec 21, 2020
Messages
45
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

error reading drive A:
Local time
Today, 22:04
Joined
May 7, 2009
Messages
11,514
see Query1 in design view.
it has a Criteria to only show records for This month, and This Year.
 

jazsriel

Member
Local time
Today, 08:04
Joined
Dec 21, 2020
Messages
45
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

Registered User.
Local time
Tomorrow, 00:34
Joined
Jul 21, 2013
Messages
65
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
 

Attachments

  • DB3.accdb
    716 KB · Views: 18

jazsriel

Member
Local time
Today, 08:04
Joined
Dec 21, 2020
Messages
45
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

error reading drive A:
Local time
Today, 22:04
Joined
May 7, 2009
Messages
11,514
see this.
 

Attachments

  • DB(3).zip
    76.3 KB · Views: 20

jazsriel

Member
Local time
Today, 08:04
Joined
Dec 21, 2020
Messages
45
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

error reading drive A:
Local time
Today, 22:04
Joined
May 7, 2009
Messages
11,514
did you really saw it, have you downloaded it and tried? my upload is different from HealthyB1.
 

jazsriel

Member
Local time
Today, 08:04
Joined
Dec 21, 2020
Messages
45
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

error reading drive A:
Local time
Today, 22:04
Joined
May 7, 2009
Messages
11,514
if you download it, then you did not noticed that Each month is in separate page?
rpt_page1.png
rpt_page2.png
 

jazsriel

Member
Local time
Today, 08:04
Joined
Dec 21, 2020
Messages
45
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.
 

Users who are viewing this thread

Top Bottom