strange problem in access. urgent!! 1

[joeythelips]

Hi,

i have an access application. part of the application is:
2 queries.
1 form
1 report.

The report is outputted to excel and is based on the 2 queries.
The 2 queries have the same parameter:
select the accounts that have fall between 2 dates: in the criteria section of the query i have the following text:
Between [Forms]![directdebit_11]![JOE] And [Forms]![directdebit_11]![tony]
These dates are entered in my form.

However, when i enter the form, and click on the command button which runs the macro to export the report, i am prompted to enter the dates twice.

I cannot understand this because all the parameters are spelt correctly.

Has anyone any ideas?

Thanks,

Joe

 

[jebry]

Hi Joe!

You will need to put #'s around your text values so Access knows that they are supposed to be dates.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[joeythelips]

Hi Jebry,

My text boxes on the form are formatted in the properties of each as short dates. Should i change this?

I also tried to put the # in in the query parameter as below, but that didn't work:

Between [Forms]![directdebit_11]![#JOE#] And [Forms]![directdebit_11]![#tony#]

Or maybe i picked you up wrong?

Joe

 

[jebry]

Hi Joe!

Sorry, I should have been clearer, try this:

Between #[Forms]![directdebit_11]![JOE]* And #[Forms]![directdebit_11]![tony]#

hth
Jeff Bridgham
bridgham@purdue.edu

 

[joeythelips]

Hi Jeff,

Thanks again, but still no luck. When i enter
Between #[Forms]![directdebit_11]![JOE]* And #[Forms]![directdebit_11]![tony]#
as the criteria, i get a message saying "the expression you entered has an invalid date value"

Joe

 

[mrf1xa]

Errm at the risk of stating the obvious you have picked up that it should be a # after [JOE] and not *??

 

[joeythelips]

hI jEFF,

Yes, i tried that already too.
Any other ideas?

 

[jebry]

Hi Joe!

Well, I did some testing in a spare db and the only syntax Access would accept is the same as what you had originally. So I am not sure what is going on. If this is a database that you can let me see, you can email it to me(preferrably zipped) and I will look at it. The only other thing I can think of off the top of my head, does the macro do anything at all besides export the report? I ask because the only time I got the error you mention is when I didn't have my form open.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[joeythelips]

All the macro does is execute an Output to Excel procedure.

I'll double check it and get back to you later.

Thanks for all your help

 

[jhall156]

Say, If you have that form DirectDebit_11 open and those controls exist on that field why are you getting a parameter prompt at all, kind, put a .value after them in your criteria JHall

 

[gbrigman]

Aren't you getting the dual date prompt because of the fact that the between statement is for 2 different fields?

 

[jhall156]

I hate to be redundant but if you're using a form's control's values for the criteria then you shouldn't (unless you've got the expression wrong) get any parameter dialogs. JHall

 

[mrf1xa]

Don't know if this helps, but I've run into a similar problem in the past and found it was to do with the focus on the form. If the focus is left in one of the date dialogue boxes, for some reason the query can't read from it and you get an error.

Usually this isn't a problem as you go off and click a button so when the query looks at the form the focus is now on the button, but if you are just sticking the dates in the form then trying to run the query from the database window, as when trying it out, this could be an issue...

Nigel

 

[Stickarm]

Have you tried running the queries themselves after entering the date parameters to make sure it is the Query that is the problem and not the report?

 

[joeythelips]

Hi everyone,

Thanks for your help.

I have overcome the problem by exporting all the queries, tables, macros , and reports, and by re-doing the forms.

It still seems strange though because i now have 2 identical databases which are working differently.

Thanks again for all the suggestions.

Joe

 

[GDGarth]

I have this problem all the time. The only way I've found to get rid of it is to delete the parameter, close and save the query, and redo it. Usually that will fix the problem, although sometimes it will work correctly, then quit working.

I also have built in functions that won't work on some machines, as well as database files that are corrupted and won't work at all.

I'm pretty sure, in my case, it's due to the fact I've got up to twenty users, and the database is all on the network (we have a lot of network problems). I'm going to try splitting the database, so each user has the interface on their computer, with the data stored on a network drive.

If your database is on a network, you might try that.