using a >= criteria in query pulling a form field

[Triacona]

Dear All,

Please help...
I have a query DcUndecidedForXDays.

This query has a field called DaysGone.
The field is as such.

DaysGone: (Date()-[UNI7LIVE_DCAPPL].[DATEAPVAL])

The criteria are as such:

>=[Forms]![MainScreen]![SrchCrit]

This gives an error.

ODBC--Call failed

I have tried

like >=[Forms]![MainScreen]![SrchCrit]

This gives blank results...incorrect.
I have tried this as well:

Like >="*" & [Forms]![MainScreen]![SrchCrit] & "*"

This also gives blank results!
Please help I am at a loss...
Thank you!!!

Thank you,

Kind regards

Triacona

 

[Andrzejek]

What value [tt]>=[/tt] what other value?

I think you need to have some values on both sides to compare, 'like' is an operator, reserved word.

Have fun.

---- Andy

 

[dhookom]

Your first criteria should work. Can you confirm DATEAPVAL is a date/time field?

Can you provide the entire SQL?

Is DATEAPVAL ever null or empty?

Is the form open with a number in the control?

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear Duane,

Thank you for your reply
DATEAPVAL is a date/time field...
SQL is as follows:

SELECT UNI7LIVE_DCAPPL.OFFCODE, (Date()-[UNI7LIVE_DCAPPL].[DATEAPVAL]) AS DaysGone, UNI7LIVE_DCAPPL.DATEAPVAL, UNI7LIVE_DCAPPL.DATEDECISN, UNI7LIVE_DCAPPL.REFVAL, UNI7LIVE_DCAPPL.PROPOSAL, UNI7LIVE_DCAPPL.ADDRESS, UNI7LIVE_DCAPPL.DATE8WEEK, UNI7LIVE_DCAPPL.DATEAPRECV, UNI7LIVE_DCAPPL.DCSTAT, UNI7LIVE_DCAPPL.DCAPPTYP, UNI7LIVE_DCAPPL.DECTYPE, UNI7LIVE_DCAPPL.DATEDECISS
FROM UNI7LIVE_DCAPPL
WHERE (((UNI7LIVE_DCAPPL.OFFCODE) Like "*" & [Forms]![MainScreen].[CmbOff] & "*") AND (((Date()-[UNI7LIVE_DCAPPL].[DATEAPVAL]))>=[Forms]![MainScreen]![SrchCrit]) AND ((UNI7LIVE_DCAPPL.DATEAPVAL) Is Not Null) AND ((UNI7LIVE_DCAPPL.DCSTAT) Not In ("NFA","PD","WDN","PPREQ","PDREM","PREAPP","COMP","PDE","CLO")) AND ((UNI7LIVE_DCAPPL.DATEDECISS) Is Null));

I have added a criteria in DATEAPVAL which is:
IS NOT NULL

Yes the form is open with a number in the text box SrchCrit.

Thank you for all your help

Thank you,

Kind regards

Triacona

 

[dhookom]

I would try hard-code a numeric value in place of the reference to [Forms]![MainScreen]![SrchCrit]. If that works, you may need to set the data type of the form control in the query parameters.

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

DEar Duane,

Thank you for all your help...
I have tried using the criteria in DaysGone:

>50

This gives me the error:

ODBC--Call Failed

Is this what you meant by hard coding?
How do I set the data type of the form control in the query parameters?

Thanks again


Thank you,

Kind regards

Triacona

 

[dhookom]

Yes, that is what I meant by hard-coding. It appears there is something else wrong with your query. I would try remove all calculations and criteria to see if it works. Then add one calculation or criteria back and try. Keep doing this.

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear Duane,

I have removed calculations - this has worked.
I also found out that the Access Time and Date format differs from this one.
DATEAPVAL is from an oracle database, and has different formating.

How do I change DATEAPVAL formating to one of accesses?

Any help would be greatly appreciated
Thank you!!

Thank you,

Kind regards

Triacona

 

[dhookom]

I don't believe you have told us how the date is formatted in Oracle.

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear Duane,

It is formatted 24-Aug-11.

I have tried DateValid:format([DATEAPVAL],"dd/mm/yyyy")
This is the access standard format.
I run this and it appears as such.

I then try
In another query using the query in which I formated DATEAPVAL.
And then created a field DaysGoneDate()-DateValid)
I run this and the DaysGone field has #error repeated per record.

I have also tried DateAdd().
This has not worked, field DaysGone has #error in it.

Please help I am really banging my head on this one!
Thank you for all your help!



Thank you,

Kind regards

Triacona

 

[dhookom]

dd/mm/yyyy" isn't the Access standard for queries. You might want to check out Allen Browne's International Dates in Access.

When you view the linked Oracle table in design view, what is the data type of the field?

You might need to try:
DateValid: DateValue([DATEAPVAL])






Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear Duane,

I will try that thanks

Thank you,

Kind regards

Triacona