Analyzing by Date

[Cliff]

Guys,

I was interested in doing some ROI studies by date range. Looks like to me I need to either 1) Have the ROI report prompt me for a date range or 2) Set up the query to look for a certain date range.

I have tried #2 with criteria such as >= 01/01/01 and <=01/31/01 and also "like" 01/??/01 but neither of these is working. I remember someone mentioning that the date field needed to be a certain format? Could that be the problem, or, is my criteria wrong?

Thanks,
Cliff

[Rick]

I enter the dates as > Jan 01 and <31 Jan 01.

Access2002 changes this to >#01/01/01# And < #01/31/01# for me.

[fred4now]

Cliff, look at the table you are querying in design view. Look for tDate and under data type make it Date/Time.

[hurrikane]

Freds got it Cliff....also...once you do dates..try days..surprising how many trn/jky run for the money on the weekends, or look for easy fields during the week.....

[fred4now]

Also Cliff, if you want to get fancy you can put

Between [Enter Start Date] and [Enter End Date]

in the criteria field of your query. Run query and it will ask you for dates. Only problem is if you don't put in a date it won't return any results and I can't remember how I did it before. (see my question under "paramater query")

So again if anyone knows how to make it return if null let us know (Donnie).

[Cliff]

Guys thanks for the help. Unfortunately none of the ideas worked. The most frustrating (and probably the key to the whole thing) was the change tDate type. Access was working on it but then stopped and gave me this error message:

MS Access can't change the data type
There isn't enough disk space or memory.

That really confuses me since I have 20gig of hard drive space left. Only about 8 months worth of data, so, hard to imagine that it's a memory problem either. I dunno.

Cliff

[Carl]

Yeah, Access loves that "memory message".

Here's a maybe workaround for you Cliff.

1) Create a new blank table with all your present fields in it. You can do this via query or by importing the present table as "definition only" under options.
2) Make sure the new table has "date/time" under properties of the tdate field. Access won't bother you about changing it with no data in the table.
3) Run an append query appending the old table with data to the new one. The data will come into the new table then with the proper field heading.

Easier than I make it sound. Give it a go, don't think you will break anything.

Later,
Carl

[MikeDee]

Cliff-
Carl is right on with his suggestion. One final step once you have completed moving the data to the new table, you can simply highlight the name of the new table and change it to the name of the table that that data came from. Access will tell that the the table already exists and do you want to replace it. Just say yes and all of your existing queries will still be able to operate as before.

[Rick]

How did you end up with a date field not in date format??

How are you importing your data? Are you using an Import Specification?

If so, check to make sure that the import specification is importing that field as date. If it is not, make the changes and save your changes.

[Cliff]

Guys,

Thanks for all the help. I finally got around to attacking this mess and found that Carl's "workaround" did work. Now with the tdate field properties correct, I tried a couple of the suggestions. Here's what I found:

1) Using fred4now's suggestion of Between [Enter start date] and [Enter end date] works great for the query (prompts me and tallies correctly), however, when going to the ROI report it prompts me for the dates but doesn't appear to pick up =DCount ("[nWin$]", etc, etc.) correctly. I verified that the query name was in correctly in both spots. Any ideas why this wouldn't work?

2) For the meantime, have used Ricks suggestion which seems to work OK in my Access 2000. Ricks, I don't know how I ended up with a date field not in date format. It appears to be that way in all my tables. I don't think I'm using an "Import Specification". If you'll tell me how to check for that and be sure it's importing that field as a date, I'll follow up on it.

Thanks again. I have a list of dumb Access questions to be posted at a later date!

Cliff

[Rick]

To check your Import Specification file you need an import file. This can be a new or old one.

1. Open db
2. Click File, Get External Data, Import
3. Change Files of type: to Text Files
4. Locate and double click on your Import File
5. Click Advanced, Specs...

You should have a file titled ALL_MSA Import Specification. Highlight and click Open.

Your date field should have a field type of Date/Time. If not you have a problem.

If you don't have the import specification file let me know and I can send you a new template.

The Macro ALL_MSA uses this Import Specification file to import the files. Normally the only change you have to make is to the path to your import file.

When importing, you can have a great table design and definition, but if the Import Specification isn't done right it can mess everything up. Actually the date field is the one that gets messed up the most.

[Cliff]

Ricks,

I don't have anything in the box under "Import/Export Specifications:". To be honest, I'm not sure how I got to this point. Never really had any problems until this tDate thing came up. Any help would be appreciated, realizing of course, that you'll have to lead me through it step by step 'cause I don't know what I'm doing.

Thanks,
Cliff

cliffhartnitt@home.com