By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,445 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

How to format query columns from VBA?

Expert 5K+
P: 8,435
Hi all.

Lunch time now, so I can spare a few minutes to throw in a question before my 2 hour meeting.

Can someone show me briefly how, from VBA, to set up a query with columns formatted the way I want?

You see, I have my big multi-database query thing working nicely. The VBA code builds the SQL string which will query the various tables and UNION the results. It then uses CreateQueryDef to create a new query in the database, then OpenQuery to open it. This all work OK, and performance is quite nice. But the formatting of the result is not really acceptable.

The two options I had in mind are
  • Look through the field definitions after creating the querydef, and see whether I can set various properties accordingly. That is, things like column width and for a couple of time fields, a Format of “hh:nn:ss” (that may be out of date – I need to look up what was suggested on theScripts when I asked about this some time back).
  • Rather than creating the new querydef from scratch each time, have a query template with appropriate formatting, which is then copied and the SQL modified each time.
Any thoughts?
Nov 20 '06 #1
Share this Question
Share on Google+
45 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
If you truely want to control formatting including column widths, I would advise creating a TableDef and inserting results into the new table.

Another option is to create a query set up to return all records assuming the fields don't change and use the following code to pass parameters to the query to get the results you want

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim dbs As DAO.Database
  3. Dim qdf As DAO.QueryDef
  4. Dim critID As Long
  5.  
  6.   Set dbs = CurrentDB()
  7.   Set qry = CurrentDB.QueryDefs("QueryName") 
  8.  
  9.   critID = 2
  10.   qdf.Parameters("ID").Value = critID
  11.   qdf.Execute
  12.  
  13.   Set qdf = Nothing
  14.   Set dbs = Nothing
  15.  
Hi all.

Lunch time now, so I can spare a few minutes to throw in a question before my 2 hour meeting.

Can someone show me briefly how, from VBA, to set up a query with columns formatted the way I want?

You see, I have my big multi-database query thing working nicely. The VBA code builds the SQL string which will query the various tables and UNION the results. It then uses CreateQueryDef to create a new query in the database, then OpenQuery to open it. This all work OK, and performance is quite nice. But the formatting of the result is not really acceptable.

The two options I had in mind are
  • Look through the field definitions after creating the querydef, and see whether I can set various properties accordingly. That is, things like column width and for a couple of time fields, a Format of “hh:nn:ss” (that may be out of date – I need to look up what was suggested on theScripts when I asked about this some time back).
  • Rather than creating the new querydef from scratch each time, have a query template with appropriate formatting, which is then copied and the SQL modified each time.
Any thoughts?
Nov 20 '06 #2

Expert 5K+
P: 8,435
If you truely want to control formatting including column widths, I would advise creating a TableDef and inserting results into the new table.

Another option is to create a query set up to return all records assuming the fields don't change and use the following code to pass parameters to the query to get the results you want
Expand|Select|Wrap|Line Numbers
  1. Dim dbs As DAO.Database
  2. Dim qdf As DAO.QueryDef
  3. Dim critID As Long
  4.   Set dbs = CurrentDB()
  5.   Set qry = CurrentDB.QueryDefs("QueryName") 
  6.   critID = 2
  7.   qdf.Parameters("ID").Value = critID
  8.   qdf.Execute
  9.   Set qdf = Nothing
  10.   Set dbs = Nothing
  11.  
Hm... I must admit I'm not keen on the idea of creating a table. Especially since this could, theoretically, be run by a number of users simultaneously, and the returned sets could be quite large.

The querydef idea sounds something like what I'm after. But I have to build the actual SQL each time, as the tables vary.
Nov 20 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Hm... I must admit I'm not keen on the idea of creating a table. Especially since this could, theoretically, be run by a number of users simultaneously, and the returned sets could be
I understand. This might also cause a problem in passing a parameter to or redefining an existing query. It might be that your only option is to create a qryDummy querydef and delete it each time. You might have to timestamp the name unless each user has their own frontend and you could create it there.

The problem is I don't know how you would control column width.

One other option which I've used in the past where users only needed to view results is using a listbox.

1. Create an unbound listbox.
2. Using vba set the row source to sql statement (your union query formatting time etc. in the query)
3. Set the column Count
4. Set the column widths

Example:

Expand|Select|Wrap|Line Numbers
  1.  
  2. list1.Rowsource = "SELECT EmpID, EmpName, Format([DOB], 'Short Date) FROM TableName WHERE [DOB] > #02/05/1980#;"
  3. list1.ColumnCount = 3
  4. list1.ColumnWidths = 1.5 cm;3 cm;2 cm
Nov 20 '06 #4

Expert 5K+
P: 8,435
The querydef idea sounds something like what I'm after. But I have to build the actual SQL each time, as the tables vary.
Sorry, submitted that before I was quite finished.

This is where I'm dynamically building the SQL to query various multi-million-record tables and UNION (and sort) the results.

Anyway, how would I go about copying an existing querydef and saving it with a new name? For that matter, is it possible to make a temporary one that I can run without saving it?

Oops! Almost time to head off to my meeting. I'll try to log on tonight from home.
Nov 20 '06 #5

Expert 5K+
P: 8,435
One other option which I've used in the past where users only needed to view results is using a listbox.
...
Interesting. I'll have to look into the possibility. I haven't had enough of a chance to play with the various form controls in Access, anyway, so it'll be fun. However, I forsee a couple of problems, based on things which I haven't mentioned yet (at least in this thread).
  • I need the user to be able to copy this output and paste it to Excel. Would a multi-column listbox work for this?
  • Is there any way I could set a date/time field to show a particular format? You see, it's not good enough to simply display the formatted string. It must be the actual numeric value (for Excel to work with), but preferably formatted into hours, minutes etc.
Also, a question or two come to mind. Such as...
  • Is the SQL executed immediately when RowSource is set, or when the display is refreshed?
  • If the former, then I assume it would be better to set the other properties first, correct?
(By the way, it turned out the meeting's actually not for another half hour.)
Nov 20 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
First:

The code to create a querydef

Expand|Select|Wrap|Line Numbers
  1.  Public Sub CreateQueryDefAndRun() 
  2.   On Error Resume Next 
  3.   'Must Close the Open query if such the case 
  4.   DoCmd.Close acQuery, "qryDummy", acSaveNo 
  5.  
  6.   Dim db As Database 
  7.   Dim qdf As DAO.QueryDef
  8.  
  9.   Set db = CurrentDb
  10.  
  11.   ' If the query is closed it can be deleted 
  12.   db.QueryDefs.Delete ("qryDummy") 
  13.  
  14.   On Error GoTo EXIT_Sub 
  15.  
  16.   strSQL = "Select FName, LName, CustPhone, CustAddress from tblCustomers" 
  17.   Set qdf = CurrentDb.CreateQueryDef("qryDummy", strSQL) 
  18.   qdf.Close 
  19.  
  20.   DoCmd.OpenQuery "qryDummy", acViewNormal, acReadOnly 
  21.   DoCmd.Maximize ' Maximizes the Query Results pane 
  22.  
  23. EXIT_Sub: 
  24.   Set db = Nothing
  25. End Sub 
  26.  

  • I need the user to be able to copy this output and paste it to Excel. Would a multi-column listbox work for this?
There are ways but it might be easiest to create the querydef at that time and output and delete the querydef immediately. This would avoid too much multiuser conflict as the query would only exist for the length of time it takes to output it to Excel.

  • Is there any way I could set a date/time field to show a particular format? You see, it's not good enough to simply display the formatted string. It must be the actual numeric value (for Excel to work with), but preferably formatted into hours, minutes etc.
If in the query you put

SELECT field1, field2, Format([TimeField], 'Short Time') From ...

Excel should recognise this as a time formatted field. Excel can be rather annoying at times because it 'intelligently' imposes formatting on columns you output. However, in this case it shouldn't be a problem. Test it our with a simple query and see.

  • Is the SQL executed immediately when RowSource is set, or when the display is refreshed?
  • If the former, then I assume it would be better to set the other properties first, correct?
No, I forgot to mention in previous post you would need to put:

list1.Requery at the end of your code.
Nov 20 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Forgot to mention ...

If you put the create query and export to excel code behind a command button. The sql querydef can be obtained by using

Expand|Select|Wrap|Line Numbers
  1.  strSQL = list1.RowSource 
  2. qdf.CreateQueryDef(strSQL)
  3.  
Get the idea?

Mary
Nov 20 '06 #8

Expert 5K+
P: 8,435
...
If in the query you put
SELECT field1, field2, Format([TimeField], 'Short Time') From ...
I don't think this is an option. It depends on a response to an earlier posting, which I need to track down and re-read.

You see, the value is the result of subtracting a couple of date/time fields. It can go over 24 hours, and using the "hh:nn:ss" format it clocks over at 24 hours. This has been a pain for some time. If I can overcome it to produce a larger number of hours than 24 (possibly even 3 digits), then I'll just output the formatted string and naturally Excel will recognise it, no problem. But as it stands at present, I need to actual number so that I can (well, the user can) reformat it in Excel.

Only 15 minutes until my meeting, but I'll go have a look for that formatting thread now...
Nov 20 '06 #9

Expert 5K+
P: 8,435
Only 15 minutes until my meeting, but I'll go have a look for that formatting thread now...
It didn't work. The suggestion, from Andrew Thackray, was to use 0 hh:nn:ss as the format. This was automatically changed to 0" hh:nn:ss" by the editor, and produced an actual displayed output of 0 hh:nn:ss for each record, which is not a lot of use.

I'd really prefer to be able to go with one of the two options I mentioned up front, if anyone has ideas on how to implement it.

Anyway, hopefully I'll have time at home tonight to work out how to copy a querydef, and how to play with the various field properties from VBA.

Off to meeting now - ciao!
Nov 20 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
I don't think this is an option. It depends on a response to an earlier posting, which I need to track down and re-read.

You see, the value is the result of subtracting a couple of date/time fields. It can go over 24 hours, and using the "hh:nn:ss" format it clocks over at 24 hours. This has been a pain for some time. If I can overcome it to produce a larger number of hours than 24 (possibly even 3 digits), then I'll just output the formatted string and naturally Excel will recognise it, no problem. But as it stands at present, I need to actual number so that I can (well, the user can) reformat it in Excel.

Only 15 minutes until my meeting, but I'll go have a look for that formatting thread now...
This won't work rules in Excel are the same as in Access. If you return a total time that is over 24 hours it will only display as the 24 hour part if there is a time format on the column. In this case you need it to go out as a string it is the only way it will format properly for display.

Did you see an earlier thread where I gave a function for calculating the time total using a function to display the output correctly in a string.

Mary
Nov 20 '06 #11

MMcCarthy
Expert Mod 10K+
P: 14,534

Expert 5K+
P: 8,435
This won't work rules in Excel are the same as in Access. If you return a total time that is over 24 hours it will only display as the 24 hour part if there is a time format on the column. In this case you need it to go out as a string it is the only way it will format properly for display.

Did you see an earlier thread where I gave a function for calculating the time total using a function to display the output correctly in a string.

Mary
I tried to post a quick response to this before leaving work, but the connection was lost and so was my reply. I didn't want to miss the train. Anyway...

The value being output at present is exactly what I want (a very small decimal number, produced by subtracting one date/time field from another - try it some time). When pasted to Excel, the actual number is carried across, not the formatted value. I then format it in Excel using "[h]:mm:ss". Outputting it from Access as a formatted string may or may not be an option in the future, depending on whether I can find a solution to the problem posted in an earlier thread. At present it is not, because the formatted string "clocks over" at 24 hours.

Taking the number and formatting it in Excel shows as many hours as necessary, even three digits in some cases. Unfortunately, I haven't managed to get this functionality from Acces sas yet. The string that Andrew Thackray suggested in the earlier thread (I think it was “0 hh:nn:ss”) doesn’t work at all. I tried “hhh:nn:ss” but this produced nonsense. (I may be mixing up my m and n entries here, as I use one in Access and the other in Excel to represent minutes - I forget which is which).
Nov 20 '06 #13

Expert 5K+
P: 8,435
Have a look at these two threads ..
Off to have a look now.
Nov 20 '06 #14

Expert 5K+
P: 8,435
Off to have a look now.
Ah, I suppose I could do a function which returns the value formatted into a string with the proper number of hours.

Isn't that what Format( ) is supposed to be for, though? I'm still hoping to find the right string to feed it. Haven't MS ever needed to deal with an amount of time longer than 24 hours?!

On the other hand, experience has shown that even the strings that Format does use sometimes fail to work as advertised. So no real surprises, there I guess.
Nov 20 '06 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
One option available is to let it go out as a decimal number then open the workbook using vba and format the column. I think you said in a previous post that you've done some vba work in excel. You can reference the Excel library in Access and use the full excel vba functionality from there.

Mary
Nov 20 '06 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
Ah, I suppose I could do a function which returns the value formatted into a string with the proper number of hours.

Isn't that what Format( ) is supposed to be for, though? I'm still hoping to find the right string to feed it. Haven't MS ever needed to deal with an amount of time longer than 24 hours?!

On the other hand, experience has shown that even the strings that Format does use sometimes fail to work as advertised. So no real surprises, there I guess.
There may be some new functionality in later versions but the older standard versions have no format for converting the digital number to a time format over 24 hours.

If you do a general search on the web you will find a lot of functions have been written to overcome this problem. I'm just wondering as per my previous post if there is something in the Excel library that will help?

Mary
Nov 20 '06 #17

Expert 5K+
P: 8,435
There may be some new functionality in later versions but the older standard versions have no format for converting the digital number to a time format over 24 hours.
Maybe, but define "older" and "later". By Access 2003, you'd think someone would have noticed this sort of problem.
If you do a general search on the web you will find a lot of functions have been written to overcome this problem. I'm just wondering as per my previous post if there is something in the Excel library that will help?
Hm... I'll try a quick search. Don't know about the Excel library thing, though. After all, surely it's the same Format ( ) function already provided in Access. How would I address it - Excel.Format or something? As you can see, I haven't had to do this before. I'll have a look around, though - could be useful, regardless of whether it works for Format.
Nov 20 '06 #18

MMcCarthy
Expert Mod 10K+
P: 14,534
Maybe, but define "older" and "later". By Access 2003, you'd think someone would have noticed this sort of problem.
Hm... I'll try a quick search. Don't know about the Excel library thing, though. After all, surely it's the same Format ( ) function already provided in Access. How would I address it - Excel.Format or something? As you can see, I haven't had to do this before. I'll have a look around, though - could be useful, regardless of whether it works for Format.
In general terms ...

Using the excel library you can open a workbook, worksheet, etc.

Reference any cell or cell range. At a guess I would say that there is functionality to format A$.

You can actually control your full output to excel in this manner.

Have a look at the following articles on msdn

http://support.microsoft.com/kb/210129/en-us
http://support.microsoft.com/kb/210148/en-us
http://support.microsoft.com/kb/210288/en-us
http://support.microsoft.com/kb/198571/en-us

Mary
Nov 20 '06 #19

Expert 5K+
P: 8,435
In general terms ...
...
Have a look at the following articles on msdn
...
Will do. In the maintime, I threw together this little function to produce the output I want. Whether it will paste to Excel correctly remains to be seen...
Expand|Select|Wrap|Line Numbers
  1. Private Const OneSecond As Double = 0.0000115741
  2. Public Function hms(TimeInterval As Double) As String
  3.   Dim Seconds As Long, Minutes As Long, Hours As Long
  4.   Seconds = Abs(TimeInterval) / OneSecond
  5.   Hours = Seconds / 3600
  6.   Seconds = Seconds - 3600 * Hours
  7.   Minutes = Seconds / 60
  8.   Seconds = Seconds - 60 * Minutes
  9.   hms = Format$(Hours) & ":" & Format$(Minutes, "00") & ":" & Format$(Seconds, "00")
  10. End Function
Nov 20 '06 #20

MMcCarthy
Expert Mod 10K+
P: 14,534
Will do. In the maintime, I threw together this little function to produce the output I want. Whether it will paste to Excel correctly remains to be seen...
Expand|Select|Wrap|Line Numbers
  1. Private Const OneSecond As Double = 0.0000115741
  2. Public Function hms(TimeInterval As Double) As String
  3. Dim Seconds As Long, Minutes As Long, Hours As Long
  4. Seconds = Abs(TimeInterval) / OneSecond
  5. Hours = Seconds / 3600
  6. Seconds = Seconds - 3600 * Hours
  7. Minutes = Seconds / 60
  8. Seconds = Seconds - 60 * Minutes
  9. hms = Format$(Hours) & ":" & Format$(Minutes, "00") & ":" & Format$(Seconds, "00")
  10. End Function
I'd be interested to know if it works. Could be very useful.

Mary
Nov 20 '06 #21

NeoPa
Expert Mod 15k+
P: 31,186
Killer, just to get in on this...
If the formatting that you need is consistent across all the queries you want to create then have a 'qryDummy' set up in your database. We will use it as a template. Then you can make a copy of this and set the SQL up as you need. A unique name for it including User & DateTime should do the trick.

If there is more changing of formats required after the new QueryDef is created then use something within
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.QueryDefs("QueryName").Fields("FieldName").
CopyObject Method
The CopyObject method carries out the CopyObject action in Visual Basic.

expression.CopyObject(DestinationDatabase, NewName, SourceObjectType, SourceObjectName)
expression Required. An expression that returns one of the objects in the Applies To list.

DestinationDatabase Optional Variant. A string expression that's the valid path and file name for the database you want to copy the object into. To select the current database, leave this argument blank. Note: In a Microsoft Access project (.adp) you must leave the destinationdatabase argument blank. If you execute Visual Basic code containing the CopyObject method in a library database and leave this argument blank, Microsoft Access copies the object into the library database.

NewName Optional Variant. A string expression that's the new name for the object you want to copy. To use the same name if you are copying into another database, leave this argument blank.

SourceObjectType Optional AcObjectType.

AcObjectType can be one of these AcObjectType constants.
acDataAccessPage
acDefault default
acDiagram
acForm
acFunction
acMacro
acModule
acQuery
acReport
acServerView
acStoredProcedure
acTable
Note When using the CopyObject method with a data access page, a copy of the HTML file for the data access page is created in the Default database folder and a link to it is created in the destination database.


SourceObjectName Optional Variant. A string expression that's the valid name of an object of the type selected by the sourceobjecttype argument. If you run Visual Basic code containing the CopyObject method in a library database, Microsoft Access looks for the object with this name first in the library database, then in the current database.
Nov 20 '06 #22

NeoPa
Expert Mod 15k+
P: 31,186
I'll try to post comments on all the other posts in here in random order - hope some comments throw some light.
BTW If you two could slow down a bit that would certainly help - OK?

Your hms function can use Format() for the minutes & seconds and add just 24* days to the hour value.
Alternatively, use Hour(), Minute() & Second() functions which do most of the work for you.

You could consider getting the data from within Excel using a query to your database.

The format string you quoted was actually suggested by me I think - but didn't work. Andrew (if I remember correctly) posted what looked like some good code as a function.

If you want the actual number, then just don't format it as a date at all, let it drop through to be formatted in Excel.

If you choose to use CreateQueryDef(), there is an option to make the result temporary (Leave Name blank) which may suit your purposes. I still think you'd be better served by copying where most of the work's already been done though.

Phew - enough already!
Nov 20 '06 #23

Expert 5K+
P: 8,435
I'll try to post comments on all the other posts in here in random order - hope some comments throw some light.
BTW If you two could slow down a bit that would certainly help - OK?
Sorry, I forgot you're a slow reader. I'll try to write s...l...o...w...e...r... :)

Your hms function can use Format() for the minutes & seconds and add just 24* days to the hour value. Alternatively, use Hour(), Minute() & Second() functions which do most of the work for you.
Both excellent suggestions, which I will try out when I have time. Thanks.

You could consider getting the data from within Excel using a query to your database.
Possibly. But typically we're just doing a query and viewing the result. I personally like to copy/paste to Excel so I can easily chart the results.

The format string you quoted was actually suggested by me I think - but didn't work. Andrew (if I remember correctly) posted what looked like some good code as a function.
Um.... yes, you're right. I just checked. Sorry Andrew.

If you want the actual number, then just don't format it as a date at all, let it drop through to be formatted in Excel.
I am copying and pasting the actual number to Excel. This is the root of the problem. If I wanted to, I could just output a formatted string with numbers of days, hours etc. But then it wouldn't paste properly in Excel. Using the Format property on the field fortunately doesn't affect the underlying data, so pasting still works alright. That's one of the reasons I have been trying to sort out how to modify the properties, so I can set the Format property on these calculated time fields.

If you choose to use CreateQueryDef(), there is an option to make the result temporary (Leave Name blank) which may suit your purposes.
That's the kind of info I was looking for. I'd love to be able to generate a querydef and run it without saving it.

If the formatting that you need is consistent across all the queries you want to create then have a 'qryDummy' set up in your database. We will use it as a template. Then you can make a copy of this and set the SQL up as you need. A unique name for it including User & DateTime should do the trick.
Argh!!!!! ;)
That was what I suggested at the start of the thread!

Anyway, time to get to work. I came in early to catch up on theScripts. :)
Nov 20 '06 #24

Expert 5K+
P: 8,435
I'd be interested to know if it works. Could be very useful.
It worked. And importantly, the result pastes into Excel successfully. This should change things a bit.

However, based on NeoPa's suggestions I've boiled it down to the following single line process
Expand|Select|Wrap|Line Numbers
  1. Private Const OneSecond As Double = 0.0000115741
  2. Private Const OneHour As Double = OneSecond * 3600
  3. Public Function hms(TimeInterval As Double) As String
  4.   hms = Int(Abs(TimeInterval) / OneHour) & ":" & Format(Minute(TimeInterval), "00") & ":" & Format(Second(TimeInterval), "00")
  5. End Function
Yes, I know I can save another step by defining OneHour directly. I'll get around to it eventually.
Nov 20 '06 #25

MMcCarthy
Expert Mod 10K+
P: 14,534
It worked. And importantly, the result pastes into Excel successfully. This should change things a bit.

However, based on NeoPa's suggestions I've boiled it down to the following single line process
Expand|Select|Wrap|Line Numbers
  1. Private Const OneSecond As Double = 0.0000115741
  2. Private Const OneHour As Double = OneSecond * 3600
  3. Public Function hms(TimeInterval As Double) As String
  4. hms = Int(Abs(TimeInterval) / OneHour) & ":" & Format(Minute(TimeInterval), "00") & ":" & Format(Second(TimeInterval), "00")
  5. End Function
Yes, I know I can save another step by defining OneHour directly. I'll get around to it eventually.
This one is definately going in my favourites. I've just started my own little thread collection of nice pieces of code various people have come up with.

Mary
Nov 20 '06 #26

Expert 5K+
P: 8,435
This one is definately going in my favourites. I've just started my own little thread collection of nice pieces of code various people have come up with.
Cool! :)

By the way, the Abs( ) function in there is the result of bitter experience. And this function in its present form won't cope with negatives.
Nov 20 '06 #27

NeoPa
Expert Mod 15k+
P: 31,186
I've fired off an e-mail request to a work colleague to send home a copy of my DB at work that has all sorts of routines in it.
I'm thinking particularly of a routine already written to create temp queries - with or without names - for the caller.
I'll see if I can give something more helpful tomorrow.
Nov 20 '06 #28

Expert 5K+
P: 8,435
I've fired off an e-mail request to a work colleague to send home a copy of my DB at work that has all sorts of routines in it.
I'm thinking particularly of a routine already written to create temp queries - with or without names - for the caller.
I'll see if I can give something more helpful tomorrow.
Thanks.

At this point though, the temp queries are not much more than a curiosity. My next step is to try and use the CopyObject (or whatever it was - I'll refer back to your post) to copy my template querydef (already created, some time ago) to one based on the user ID (and maybe date/time, or something) and then I'll modify the SQL. This should give me exactly what I want.

Since I'll be creating and deleting all these queries, I think I'll extend the split database concept a little further. At the moment, my description lookup table is in the front-end database (which is shared, not one per user). I think I'll move it out to one of its own, in case I want to compact things from time to time. This should be pretty trivial, since all the tables it's joined to are linked ones already.
Nov 20 '06 #29

NeoPa
Expert Mod 15k+
P: 31,186
Try a version where the Description table is in with the other data for the year first though (I humbly suggest). I suspect the performance is worth checking against other concepts. I've had experience where SQL runs against tables from other sources (mainly ODBC rather than Access - but I would expect similar effects) and it loses it's ability to optimise properly.
Nov 20 '06 #30

Expert 5K+
P: 8,435
Try a version where the Description table is in with the other data for the year first though (I humbly suggest). I suspect the performance is worth checking against other concepts. I've had experience where SQL runs against tables from other sources (mainly ODBC rather than Access - but I would expect similar effects) and it loses it's ability to optimise properly.
No offence, but I might pass on this one. :)
The whole point was to get it out of there, to save space due to the huge amount of redundancy between the different years' data.

Admittedly, extracting the descriptions to a lookup table within each one-year database did save a bit of room, but not enough to be worth the bother. Moving it to a separate table shared by all years has saved me about half a gig more, and performance seems reasonable. Plus I have some ideas I want to play with on the description table, and having it separate will make this much easier.
Nov 20 '06 #31

NeoPa
Expert Mod 15k+
P: 31,186
Of course no offense.
Just throwing up some ideas - sounds like you have tested it out to a certain extent already - as much as you need anyway.
Nov 20 '06 #32

Expert 5K+
P: 8,435
Oh rats!

As you know, I've now got my VBA function formatting the elapsed-time values into [h]:mm:ss format (to quote Excel's syntax), and thought everything was fine. Well, no such luck.

I just copied and pasted some results over to Excel, and discovered that they only look correct. They are pasted in as strings rather than numbers. which means that summing, charting etc don't work. Blah!

What's annoying is that if I just hit F2 to edit the cell, then [Enter], it converts it to exactly what I want.

I'll have to mull this one over for a bit. It won't be simple to get Access to send the stuff to Excel, as it isn't being placed anywhere consistent.

I don't suppose anyone has further thoughts on how to get the Format( ) function in Access to do its job?
Nov 21 '06 #33

MMcCarthy
Expert Mod 10K+
P: 14,534
As per my previous post on Automation of Excel from Access:

Example of the kind of function that can be run from access after the workbook has been opened of course. I know we dismissed it but I think it's the only way you'll be able to achieve what you want.

Mary

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Worksheet_Change(ByVal Target As Range)
  3. Dim vVal
  4.     If Target.Cells.Count > 1 Then Exit Sub
  5.     If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub
  6.  
  7.      With Target
  8.  
  9.          vVal = Format(.Value, "0000")
  10.           If IsNumeric(vVal) And Len(vVal) = 4 Then
  11.             Application.EnableEvents = False
  12.             .Value = Left(vVal, 2) & ":" & Right(vVal, 2)
  13.             .NumberFormat = "[h]:mm"
  14.           End If
  15.     End With
  16.  
  17.      Application.EnableEvents = True
  18. End Sub
Nov 21 '06 #34

Expert 5K+
P: 8,435
As per my previous post on Automation of Excel from Access: ...
Thanks, I'll file it away to have a look at when I've got a minute.

Whether I end up using it or not, it's bound to be useful as an introductory exercise. I haven't done any of this kind of automation from Access, only from VB6 (and little enough of that).
Nov 21 '06 #35

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks, I'll file it away to have a look at when I've got a minute.

Whether I end up using it or not, it's bound to be useful as an introductory exercise. I haven't done any of this kind of automation from Access, only from VB6 (and little enough of that).
Yes I've done enough to know how useful it could be in the right circumstances. But I haven't had cause to explore it fully yet.

Mary
Nov 21 '06 #36

NeoPa
Expert Mod 15k+
P: 31,186
Killer,

If I understand you correctly, what you need to do (see earlier post) is to ignore the format when copying / pasting.
If you copy the results of the calc, then it may look stupid but the underlying number should be what you want. You're trying to go full circle around what you already have.

Alternatively, to convert a number of strings (rather than F2; Enter in each cell), you can use Ctrl-H (Replace) to replace a character common to all strings, to itself. This will have the effect you want.

EG. Strings may be 999:22:11 or similar.
Replace : with : across the range and each changed cell (all of them) will be re-entered as if by the operator and reinterpreted.
Nov 21 '06 #37

Expert 5K+
P: 8,435
If I understand you correctly, what you need to do (see earlier post) is to ignore the format when copying / pasting.
If you copy the results of the calc, then it may look stupid but the underlying number should be what you want. You're trying to go full circle around what you already have.
I don’t believe so.

You see, the original intent was to produce output for viewing simply by opening the query in Access. This works reasonably well, by applying the "hh:nn:ss" format to the calculated field.

It was then decided to (also) copy and paste the details from time to time into Excel, for charting/documentation/archival/whatever purposes. Because the value being pasted across is a number, all one need do is apply an appropriate format in Excel, and all is well. (There are sums, averages and so on happening over there.)

Later still, I realised that the values displayed in Access were not always correct, as they clock over at 24 hours (and presumably 48, 72, etc). What I've been looking for ever since is a way to overcome this in Access, without affecting the paste-to-Excel process. Note that the values pasted there are correct, because they are the actual numbers, to which formatting is applied later.

Alternatively, to convert a number of strings (rather than F2; Enter in each cell), you can use Ctrl-H (Replace) to replace a character common to all strings, to itself. This will have the effect you want.
Ahah! Now, that’s useful information. I might even set up a macro on a button or something, to do the replacement. Thanks.

After all, since we already have to apply a format in Excel, it will just be a matter of replacing one step with another.

Some day soon (possibly while on holidays over Christmas, if I ever actually turn the computer on) I’ll start looking into ways to automate the process and remove some manual steps. But for the moment, presenting a string value through my hms( ) function in Access and using your replacement idea in Excel should provide what we need.
Nov 21 '06 #38

NeoPa
Expert Mod 15k+
P: 31,186
I'm pleased that's helped.
Sorry about forgetting the earlier bit, I'm sure you explained that all clearly before.
Nov 21 '06 #39

Expert 5K+
P: 8,435
I'm pleased that's helped.
Sorry about forgetting the earlier bit, I'm sure you explained that all clearly before.
Probably. I don't remember either.

Anyway...

I've tried copying a querydef and changing the .SQL property (or whatever it's called, can't be bothered checking right now. It works, but it doesn't achieve what I was after. It appears to lose the formatting. :(

In other words, I set up the "template" query in Access with the column formatting and everything exactly the way I want (widths, etc.) and save it. Then this VBA code builds an SQL string, copies the template to a new query, modifies the .SQL property then opens it. The new query runs, and produces the results I want. Except that it has forgotten about my column widths and whatever else I set.

I wonder whether this problem is inherent in the copy-and-modify-SQL technique, or is related to the UNION query that I placed in there. Because if you recall, the GUI is not available when working on UNIONs - you have to work in the SQL view. Perhaps everything related to it is discarded the instant Access sees the dreaded "U word".
Nov 21 '06 #40

MMcCarthy
Expert Mod 10K+
P: 14,534
This essentially rewrites the query, nothing other than the name is saved. Can't you put the formatting in the sql you build in VBA?

Mary

Probably. I don't remember either.

Anyway...

I've tried copying a querydef and changing the .SQL property (or whatever it's called, can't be bothered checking right now. It works, but it doesn't achieve what I was after. It appears to lose the formatting. :(

In other words, I set up the "template" query in Access with the column formatting and everything exactly the way I want (widths, etc.) and save it. Then this VBA code builds an SQL string, copies the template to a new query, modifies the .SQL property then opens it. The new query runs, and produces the results I want. Except that it has forgotten about my column widths and whatever else I set.

I wonder whether this problem is inherent in the copy-and-modify-SQL technique, or is related to the UNION query that I placed in there. Because if you recall, the GUI is not available when working on UNIONs - you have to work in the SQL view. Perhaps everything related to it is discarded the instant Access sees the dreaded "U word".
Nov 21 '06 #41

Expert 5K+
P: 8,435
This essentially rewrites the query, nothing other than the name is saved. Can't you put the formatting in the sql you build in VBA?
Please, just don't go there. :(
Nov 21 '06 #42

MMcCarthy
Expert Mod 10K+
P: 14,534
Please, just don't go there. :(
Sorry.

But guess what

That brings us back to the table vs query argument again.

Don't you just love it.



Mary
Nov 21 '06 #43

NeoPa
Expert Mod 15k+
P: 31,186
I just ran the theory through my test database and it worked fine.
I can only assume that, as you (and I) suspected, it's because of the UNION type query not being supported by the graphical interface of Access.
In these circumstances, I would design a holding query to display the values of the query you want to use (could be the copied QueryDef or whatever) as a source. The holding query would have all the settings set as you require.
Nov 22 '06 #44

Expert 5K+
P: 8,435
That brings us back to the table vs query argument again.
Yep.

Don't you just love it.
Nope.

I need to reassess exactly how I'm going to do the whole thing. I've got so many options, and the discussion has been flying off in so many directions, that I need to step back and look at the bigger picture. Lots of options popped up to address issues arising from other options, and so on, all overlapping, going in circles...

I'll let you know when I've had a chance to go over it from the start. There are probably only two basic principles, really.
  • Need the see query properly formatted (including calculated fields) in Access.
  • Need to be get (some) results into Excel in useable format.
Just let me play with this this one for a while, and simplify things.
Nov 22 '06 #45

Expert 5K+
P: 8,435
...I would design a holding query to display the values of the query ... The holding query would have all the settings set as you require.
Good idea. I'll include it in the review (see prior post).

Thanks.
Nov 22 '06 #46

Post your reply

Sign in to post your reply or Sign up for a free account.