Dates as Field Names

benallen002

Board Regular
Joined
Feb 16, 2005
Messages
65
Hey guys,

I know it is possible to in design mode, but is there anyway to create a table that has dates as it's field names through VBA code or SQL? I can't seem to find a way to do it. But why will it let you do it in design mode, but not from anywhere else?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

benallen002

Board Regular
Joined
Feb 16, 2005
Messages
65
If you read another post I have on here regarding a problem I'm having creating a report that is based on a date range, you will get the back story. The short story is that I found an easy way to do what I'm trying to do, but involves creating a table and having the fields name dynamically with each date in the range. Like I said, if I take the time to input each date as a field, my report opens and runs great. But SQL won't let me create the table and name the ranges as dates.

Otherwise, if you could take a look at my other post and see if there is a different way to do what I'm trying to do, then I would except all the help I can get.

thanks

Ben
 

MyBoo

Board Regular
Joined
Aug 9, 2006
Messages
217
Although I ask the same question Giacomo did.....

Create a query.
Enter SQL view.

SELECT YourTable.YourField AS [10/12/06] INTO NewTableName
FROM YourTable;

Trying to do this in VBA, you'll probably have to assign the Date to a string.

PS. I cannot think of ANY reason that one would want to do this. It surely can't be viewed as a good design practice.
 

benallen002

Board Regular
Joined
Feb 16, 2005
Messages
65
thanks for the help MyBoo, although this did the trick in regular SQL, I can't pass it through VBA without getting a syntax error. I tried assigning the date to a variable (the whole point of this anyways, and even that didn't work. VBA just will not let you do this I guess. Oh well.

On the subject of why I'm doing this. Simply put, I have a union query that draws from 3 seperate cross-tab parameter queries. I CANNOT find anyway to run a report based off of this union query. While I can load the query in as the Recordsource for the report, I cannont find away to use the field names in the query because VBA acts as if they are not there for whatever reason. I can't use any code relating to the field names in this union query, and I need them to set the ControlSource property of most of the controls on the report. Anyways, i found out that while it won't find field names on my query, it will on a regular old table. So I wanted to write some code that would create my table before running the report so the code could reference the field names on the table, which would be the same as the ones on my union query.

Does this make sense to anyone else?
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi

I'm not sure this matches 100% but I wrote a script to take the contents of a query and turn them into report labels and data points in a report that was based on a crosstab query. You can see the full script here :

http://www.mrexcel.com/board2/viewtopic.php?t=227640

There are limitations with this and I have made comments throughout the code where you will need to adapt this for your situation. This was built with one query in mind, not three, although I assume the report will be based on only one crosstab query?

HTH, Andrew
 

Forum statistics

Threads
1,136,651
Messages
5,677,004
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top