Help with queries that runs directly but not from code?

[ChrisOjeda]

I have a query that needs to compare two fields to the first of the month. It ran before but required manual updates each month. This is the expression used before:

IIf([ACQUISITION_DATE]>#4/30/03#,1,IIf([NEW_LOAN_SETUP_DATE]>#4/30/03#,1,0))
With the where criteria for this field to "1"

I wrote this expression to automate the query...

IIf([ACQUISITION_DATE]>=[Forms]![frmDailyDelinquencies]![txtFirst].[Value]
,1,IIf([NEW_LOAN_SETUP_DATE]>=[Forms]![frmDailyDelinquencies]![txtFirst].[Value],1,0))


Where the value of [Forms]![frmDailyDelinquencies]![txtFirst].[Value] is the first day of the current month. Code accomplishes this in the on load fuction of the form. The text box displays the first of the month. When I run the query directly (instead of through code) it works. When I run through code the program just stops at that point (does not crash so I have to break it to stop it).

Maybe there is an easy way to do this. I basically just need to make sure that:
[ACQUISITION_DATE]>= first of current month
-or-
[NEW_LOAN_SETUP_DATE]>= first of current month

Can someone please help???

 

[scriverb]

I am going to bet that the controls being referred to by your IIF statements have not been filled yet with a date value. Put a STOP command in just prior to your IIF statement and then through the Immediate Window check out the values that you think are there.

Just put:
?[Forms]![frmDailyDelinquencies]![txtFirst].[Value]
into the Immediate window and click Enter. The value of the control will be displayed. Do the same for the other control.

Let me know if you need more assistance.


Bob Scriver

 

[Darrylles]

Hi,

IF statements would tell you where the problem actually lies.

You don't really need .Value for textbox fields as long as you refer to the correct textbox name.

You can check for textbox 'values' BEFORE you run the RUNSQL 'sql code'.

Regards,

Darrylle





"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[ChrisOjeda]

First, I do know for a fact that the text control does have the right information as it is plain and visible. And when I am not running my Access application the query DOES WORK.

There is an issue and it has to do with something that is going wrong during run time. How does my query being ran manually from access (not during run time) differ from running is via the open recordset command? That is where my issue is???

 

[scriverb]

Give this a try:

IIf([ACQUISITION_DATE]>=CDate([Forms]![frmDailyDelinquencies]![txtFirst])
,1,IIf([NEW_LOAN_SETUP_DATE]>=CDate([Forms]![frmDailyDelinquencies]![txtFirst]),1,0))

Because the Unbound text boxes have a string entry that looks like a date it still needs to be converted to a date value. Either CDate or DateValue functions will perform this for you.

Now there is an easier way to do this if you can tell me when this report will be run. Will the user during the month of May being running the report that compares to 5/1/2003 or last month or next month. If there is a reliable pattern here for requesting this report then we do this with code. We can create the first of the month date value with code and not require the user to enter anything.

Bob Scriver

 

[ChrisOjeda]

Bob,

I'll give that a try. Actually I would prefer the user does not type anything. The way I have it now is they click a single button and it runs about 20 reports (data is sent to Excel). Currently this report is the only that requires user intervention. Once a month they have to hard code the expression with the first of the month for this particular query. Though my goal has been to even remove that step so that the query is always using the first of the current month as a condition when checking either of these two date fields. If you have an easier way I am all ears

 

[scriverb]

This IIF will always provide the first of the current month:

IIf([ACQUISITION_DATE]>=DateSerial(DatePart("yyyy", date), DatePart("m", date), 1)
,1,IIf([NEW_LOAN_SETUP_DATE]>=DateSerial(DatePart("yyyy", date), DatePart("m", date), 1),1,0))

Give this a try and let me know if it is what you are looking for. This should make it much easier on your users.




Bob Scriver

 

[ChrisOjeda]

That works for me! I had to add "()" after "Date" because it kept giving me a pop-up asking for the value of Date. After that it worked perfect. Thanks a lot... I am going to name my first born Robert. If it is a girl, then Roberta... Chris

IIf([ACQUISITION_DATE]>=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1),1,IIf([NEW_LOAN_SETUP_DATE]>=DateSerial(DatePart("yyyy",Date()),DatePart("m",Date()),1),1,0))

 

[scriverb]

Glad to be of assistance and I am honored. Good luck with your project.

Bob Scriver