Replies

The time zone is set correctly for Central time. This is the only ‘date’ function which displays the wrong date. Most puzzling.

Hans,
Thanks so much for the query. We’ll play with that and see what it spits out before we try anything more.
With much appreciation,
Judy

Hans,
Thanks so much for the query. We’ll play with that and see what it spits out before we try anything more.
With much appreciation,
Judy

Unfortunately, ran out of space even with upper limits set to 4095 Meg. But I know it works – just not with the size files we tried.
Thanks for your knowledge!

Windows 2000. Paging file size for selected drive – Drive space available 53595MB. Initial size 768 MB. Maximum size 1536 MB.
Registry size 28MB. Maximum registry size 55MB.

OK – size is obviously a factor. I made this work with a crosstab of 133,000 rows, but then I tried it on a crosstab of 1.9 million rows. The crosstab worked, slowly. But when making the table from the crosstab, I get an error message that there’s not enough space on the temporary disk. I’m working off a server drive. I’m not sure what the ‘temporary’ disk is or how I can make it have more space.
Any suggestions?

Thank you one and all. I was obviously too impatient. My table has over 500,000 rows so it’s too large to copy/paste. The resulting crosstab is 133120 rows. It took 9 minutes for my system to put the crosstab qry in a new query that I could ask to make a table, and another 9 minutes for that 2nd table of 133120 rows to be created. Whew! But it works, and that’s what I needed to be able to do.

Again, thanks for your knowledge and support.

Hi, Wendell,
The hourglass just sits there, and nothing happens. If I check task manager, it says that Access is not responding. I tried waiting for 5+ minutes – then gave up and ended task. It has done that every time- I choose new query, based on the crosstab, and it stops the system.

I can’t copy/paste because of the size- way too big for the clipboard.
Perhaps I’m doing something wrong? As simplistic as it sounds, could you walk me through it?
I have the table, and I have the cross tab. I assume I should make a new query, based on the crosstab? I know it doesn’t work to open the crosstab in designview and try it that way.
Any help is, of course, greatly appreciated.

Jezza- I tried to make a new query from the crosstab, and it just shuts down my system. Perhaps it’s because it’s a crosstab to a linked table? I really don’t want to import the table because dbase files that large cause their own unique complications. I obviously don’t know enough. Suggestions?

How does one change it from crosstab to make table? I tried to click on make table with the crosstab open in design view, and it did make a table, but the table did not have ID1, ID2, Code 1, Code 2, Code 3 etc in the row. Instead it hareplicated the tale underlying the crosstab.

I tried it again, and while it doesn’t ask for the second set of dates, it also doesn’t display correctly. For me, it displays on the report as Name?
I know my syntax is correct – can you see anything wrong?
=”For Medication Events occurring between ” & [Forms]![frmDates]![txtStartDate] & ” and ” & [Forms]![frmDates]![txtEndDate]

I thank you for your continued support on this.

Thank you so much. I’m not sure why that works and the other method does not, but I certainly can work this.
However, I was also using my [start date] and [end date] in the report headers…. = ” For Events Reported Between “&[start date]&” and “&[end date]. I’ve tried substituting the [Forms]! statement but that doesn’t seem to work. I can enter the dates twice, but if possible would prefer only one entry. Do you have any thoughts/ suggestions?
Again, I really do appreciate the help.

Forgive my ignorance, but how does making a form to enter the date range criteria, rather than using criteria of ‘Between [date1] And [date2]’ for that field, enable me to have a date field in the crosstab? I ‘ve never gotten crosstabs to allow fields that don’t have a criteria in them, so I don’t see how I can put the parameter in the crosstab regardless of whether it uses a form or some other means – or am I missing something?

I’m sorry. I am not familiar with using a form or report as part of a query. Do you have an example I could look at?
I’m attaching a couple of the SQLs, and an example of what the end product looks like. Thank you for helping on this- it’s an ongoing issue since our data is so date-dependant and the reports we run tend to use date as the only criteria.

From everything I’ve read, I would agree that split -end dbs are preferable. My IS department is not knowlegable about databases, and does not really involve itself in software at all other than to dictate what/ which version. Nonetheless, I will keep working on them to understand.

I thank you all for your support as I struggle to learn more about Access and Visual Basic code.
Judy