UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Crosstab Report Not Recognizing Date Fields, Access 2016    
 
   
ovengravel
post Jul 16 2019, 06:56 AM
Post#1



Posts: 4
Joined: 16-July 19



Hi,
This is a crosspost from another forum, but I didn't get a response there. Not sure if this is a query or report question, but:

I have a client who has just converted an old Access 2003 database to Access 2016. Most of it is working fine. However, he has a report that is based on a table that contains fields named "Month1", "Month2", etc. up to "Month12". These fields are populated in VBA by an append query. The source of the append is a crosstab query with dates as the columns. The SQL is something like "INSERT INTO tblRecordSource (Month1, Month2, Month3 ... ) SELECT [1/1/2010], [2/1/2010], [3/1/2010] ... FROM qryCrosstab"

The problem is that Access is telling us it does not recognize [1/1/2010] as a field name. Apparently it used to in Access 2003.

Any idea what might be failing here?

Thanks.
Go to the top of the page
 
Kamulegeya
post Jul 16 2019, 07:42 AM
Post#2



Posts: 1,849
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa


Hi welcome2UA.gif

What happens if you try to open the crosstab query itself?

Does it open and display data when opened?

Ronald
Go to the top of the page
 
ovengravel
post Jul 16 2019, 07:54 AM
Post#3



Posts: 4
Joined: 16-July 19



Yes, the query works fine. It is only when I try to populate a table in VBA by referencing the field name that I get an error message.
Go to the top of the page
 
Kamulegeya
post Jul 16 2019, 07:59 AM
Post#4



Posts: 1,849
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa


How about if you save the VBA string into an actual query and try to run it?

Does it work or fails too?
Go to the top of the page
 
ovengravel
post Jul 16 2019, 08:05 AM
Post#5



Posts: 4
Joined: 16-July 19



The crosstab SQL string generated by VBA does work when you paste it into a query. But the append query still won't work.
Go to the top of the page
 
Kamulegeya
post Jul 16 2019, 08:10 AM
Post#6



Posts: 1,849
Joined: 5-September 10
From: Kampala,Uganda The Pearl of Africa


I suggest that you execute the saved query using the querydef object in vba.

Try it and give feed back.

I mean the insert query based on the crosstab.
This post has been edited by Kamulegeya: Jul 16 2019, 08:11 AM
Go to the top of the page
 
NimishParikh
post Jul 16 2019, 01:42 PM
Post#7



Posts: 224
Joined: 30-November 10



Would you like to revisit the database design itself. Why you should have Month1, Month2 as a field instead of having a single field Month with multiple records.

Nimish
Go to the top of the page
 
cheekybuddha
post Jul 16 2019, 02:15 PM
Post#8


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


welcome2UA.gif

Does your database compile after the upgrade? (And do you have Option Explicit declared at the top of all your modules?)

Are you using code run the append query to populate the temp table tblRecordSource? If so, please post it (the whole code)

--------------------


Regards,

David Marten
Go to the top of the page
 
strive4peace
post Jul 16 2019, 02:45 PM
Post#9


strive4peace
Posts: 20,447
Joined: 10-January 04



hi (what is your name?)

re: why it doesn't work now?
Older versions of Access were more forgiving.

Please consider Nimish's comment about the database structure... when data is separated into Month1, Month2, or "January", "February", etc, the only way to sum it is to add all the columns -- better to have more records and less columns. However, this is a crosstab, so perhaps it is ok (as cheekybuddah noted below)!

It good practice says to start fieldnames with a letter, and to not include special characters such as "/" because / means division, and isn't allowed in a good name* ... calculated fieldnames too. When the crosstab is used as a source for something else, specify your own good (generic!) field names in the underlying SQL. You didn't post the SQL for your query, but am assuming that it is limited to months for just one year, corresponding to Month1 for January, Month2 for February, and so on. Please share what you are using (as cheekybuddha also asked) so that we can suggest an alternative to specify your headings in the PIVOT phrase of the SQL statement ("Column Headings" on the property sheet for the query), thank you.

Being able to open the crosstab at look at data is the good first step to test, as Ronald suggested. Once that can happen and if the next part fails, then column headings often need to be specified.

* 1/1/2010 = 0.0004975124

Summary:

[1/1/2010] is not a good field name. It starts with a number, has special characters, and isn't generic -- what happens when the year changes? Specify generic field names using the Column Headings property in the query design.

Each control on a report has a Control Source that specifies the field where the data comes from. This is not position dependent; it is name dependent; and when dynamic names created by the crosstab change, such as the year changes, then calculated field names will be different than they were when the report object was defined. You want to use generic names for control sources so that when contents of the crosstab change, the field names do not.


This post has been edited by strive4peace: Jul 16 2019, 03:43 PM

--------------------
have an awesome day,
crystal
Go to the top of the page
 
cheekybuddha
post Jul 16 2019, 03:03 PM
Post#10


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


The structure seems OK if I'm reading this right. ovengravel is denormalising from a proper structure via a crosstab query in to a (temp) table to be used as the RecordSource for display in a report.

--------------------


Regards,

David Marten
Go to the top of the page
 
strive4peace
post Jul 16 2019, 03:50 PM
Post#11


strive4peace
Posts: 20,447
Joined: 10-January 04



imo, the problem you are having is that the update query doesn't like the field names. You can get around this issue by defining the fieldnames to use. Please post the SQL for qryCrosstab so we can help you modify it

also, you are appending this data to tables that should perhaps be normalized ... ? Would you please tell us about what kind of data this is and how you will be using it? thank you ~


This post has been edited by strive4peace: Jul 16 2019, 04:06 PM

--------------------
have an awesome day,
crystal
Go to the top of the page
 
ovengravel
post Jul 21 2019, 04:56 PM
Post#12



Posts: 4
Joined: 16-July 19



Thanks to everyone for the suggestions. It turned out that the code (which I did not write) was mixing references to [1/1/10] and [1/1/2010]. It was difficult to spot because the code was pretty dense, combining several crosstab queries (each created in code) into complicated recordsources for several different reports. I agree, not a good way to name fields.

Thanks again!
Go to the top of the page
 
strive4peace
post Jul 25 2019, 03:42 PM
Post#13


strive4peace
Posts: 20,447
Joined: 10-January 04



glad you've got it working again!

--------------------
have an awesome day,
crystal
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st August 2019 - 10:39 AM