Query Prompt

[DBFIN]

My company recently upgraded our computers from Windows XP to Windows 7. After this upgrade, a very simple MS Access 2010 query would no longer run. The query, QUERY 10, simply prompts the user to enter a value in the policy effective date field with the following criteria: >=[Enter Monthend Date]. I receive an error message that Access can not find the name entered in the expression, which is the date 4/30/2014.

This query is in a series of queries that also prompt for dates. All the prior queries work successfully until in reachs this query. The prompt appears as if its already populated with a date that was entered from a prior query, QUERY 9, which means the prompt key is not clearing prior values.

Query 10 runs fine if I open it manually, but fails when executed via a run macro action.

 

[spikepl]

show the sql

 

[DBFIN]

Open Query

DoCmd.OpenQuery "B1g Identify Initial Policies", acViewNormal, acReadOnly

Criteria in Policy Effective Date Field
>=DateValue([Enter Monthend Date MMDDYY])-90

 

[spikepl]

Show the SQL of the query. Open the query, select the SQL view and paste the content here. (And concerning the naming of objects read this: http://www.access-programmers.co.uk/forums/showthread.php?t=225837 )

 

[DBFIN]

I'm embarassed to admit that I've lost a lot of time on this issue. The database worked perfectly before we migrated to new operating system. All queries with a prompt fail if executed from a macro but work if executed individually outside of a macro.

 

[DBFIN]

SELECT [Brokerage Production].Pol_idx, [Brokerage Production].Item, [Brokerage Production].Trans AS Trans2, [Brokerage Production].Eff INTO [Brokerage_Initial Policy List1]
FROM [Brokerage Production]
WHERE ((([Brokerage Production].Trans) In ("NEW","REN")) AND (([Brokerage Production].Eff)>=DateValue([Enter Monthend Date MMDDYY])-90));

 

[spikepl]

Is [Brokerage Production] a table or a query? If it is a query show that too... and any other nested queries.

 

[DBFIN]

Brokerage Production is a table. The criteria with a prompt reads the eff field of this table, which is in date/time format.

 

[DBFIN]

Keep in my that this all worked for over 5 years before the company migrated us to Windows 7.

 

[spikepl]

I am not too sure about this, never using macros and never using prompts ... try replacing your sql by this:

PARAMETERS [Enter Monthend Date MMDDYY] DateTime;
SELECT [Brokerage Production].Pol_idx, [Brokerage Production].Item, [Brokerage Production].Trans AS Trans2, [Brokerage Production].Eff INTO [Brokerage_Initial Policy List1]
FROM [Brokerage Production]
WHERE ((([Brokerage Production].Trans) In ("NEW","REN")) AND (([Brokerage Production].Eff)>= [Enter Monthend Date MMDDYY]-90));

 

[DBFIN]

I had already tried your suggesting. I believe your change was to take out the "DateValue". When I break down the macro into a series of macros with these prompt queries run separately, everything works fine. This means all the syntax is fine.

 

[spikepl]

Errr huh?

I believe your change was to take out the "DateValue".

I am not into religious beliefs. I provided you with sql to try in its entirety as is presented here ... if you do not wish to do that then fine.

 

[DBFIN]

I greatly appreciate all your help with this. I discovered the issue. The Set Warnings No command interferes any query criteria that creates a prompt. I changed the Set Warnings action property to Yes before the query and the query works fine.

Thanks again for all your help !!! Your expertise is priceless !!