Date Submitted Query (Continuation)

[iuianj07]

iuianj07 (Programmer) 25 Sep 09 11:42
Okay, I have a query wherein the field name is Date Submitted (Date Format)...

If we have submitted the said work, we would put the date in the Date Submitted Field, if it is still in process, it is blank... and there's a checkbox for Withdrawn if the work has been withdrawn and we didn't continue anymore the work....

Then at the end of the week, we submitt a report of the status of each work if it's already been Submitted, In Process or Withdrawn..

I wrote a query:
Date Submitted: IIf([Lease Tracking]![Date Submitted] Is Null And [Lease Tracking]![Lease Consent Withdrawn]=0,"In Process",[Date Submitted])

what this do is that it automatically puts "In Process" whenever the Date Submitted field is blank and the Withdrawn checkbox is not checked.

The results is correct except that for the work that has the Withdrawn checkbox checked.. it just shows up as blank field..

Can you help me re-write this query/iif statement that should result having "Withdrawn" on the Date Submitted Field if the Withdrawn Checkbox is checked.

Thank you,


TheAceMan1 (Programmer) 25 Sep 09 12:59
How are ya iuianj07 . . .

Try this:

CODE
Date Submitted: IIf(IsDate([Lease Tracking]![Date Submitted]), [Date Submitted], IIf([Lease Tracking]![Lease Consent Withdrawn]=0, "InProcess", "Withdrawn"))


iuianj07 (Programmer) 12 Nov 09 13:59
Hello TheAceMan1 (and whoever reads this and would be able to help me),

I really appreciated the help you gave me last time,, I hope you could help me once more which is a continuation of this query.

With this report that I submitt, it shows ALL of the "Date Submitted". We want it now to just filter it to a specific month. for example we want to show Date Submitted for only the month of November.

I tried to put Between [1st day of the Month] and [last day of the month] criteria below the iif statement you helped me with, and the results are it only shows the ones that have been submitted, the ones that are "In Process" doen't show up?

I tried to filter it in different ways like pulling down the Date Submitted data field and put a Between [1st day of the Month] and [last day of the month] criteria but all to no avail, I hope you could read this and help me out. I really appreciate it.

Thank you very much

 

[dhookom]

I have two issues with:

Date Submitted: IIf(IsDate([Lease Tracking]![Date Submitted]), [Date Submitted], IIf([Lease Tracking]![Lease Consent Withdrawn]=0, "InProcess", "Withdrawn"))

First, I'm not sure how this is possible since "Date Submitted" is used as an alias when it is already a field name in the query. I didn't think Access would allow this.

Second, your expression might return either a date or text data type. I don't allow this in my applications. Typically an expression should return a consistent data type or null.

I would keep the Date Submitted field as it is and add another field that returns text. You could also do the expression calculation in your form or report.

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

I know what you mean by your first concern:

I just cut out the last part of the field name (confidential reasons) but it should have been "Date Submitted to A: IIF(IsDate([Lease Tracking]![Date Submitted to Ab]), etc... sorry I wasn't clear about that.

With regards to the expression that Mr. TheAceman1 wrote, it worked perfectly in having the dates plus In Process whenever it has yet to be submitted. The problem now is, it should be now filtered to a specific month.

Thanks for the help dude!

 

[dhookom]

If you want something filter to a specific month then you need a date or month number column/field to use for the filter. Your expression doesn't return a date or numeric value. It returns a string.

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

Yes, the "Date Submitted to Ab" is a date/month field. I am able to filter for a specific month when I put "Between [1st day of the month] and [last day of the month]" criteria at the bottom of this expression, it does filter for the correct month, however, it now doesn't include the ones that have yet to be submitted therefore "In Process

 

[dhookom]

I would think that if you viewed your SQL of the query, the where clause might look like:

WHERE [Date Submitted to Ab] BETWEEN Forms!frmDates!txtStart AND Forms!frmDates!txtEnd Or ([Date Submitted to Ab] Is Null AND [Lease Consent Withdrawn]=0)

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

I was able to solve my problem now, I am glad!

But still, I was able to put the pieces of the puzzle because of your help dhookom, so thanks to you!

In case you wanted to know how I figured it out, I'll give a brief explanation:

I did have my [Date Submitted to Ab] field set into a date, however, I think based on the expression TheAceMan1 wrote before.. IIf(IsDate([Lease Tracking]![Date Submitted]), [Date Submitted], IIf([Lease Tracking]![Lease Consent Withdrawn]=0, "InProcess", "Withdrawn"))

the text in bold converts the Date Submitted to Ab into a date format in case it wasn't formatted that way..

with that in mind, I changed the field type for Date Submitted to Ab into a Text Field. Then in my query I pulled down the Date Submitted to Ab field again, and set my criteria to filter for a specific month OR Is Null.

What it does now it asks for the range of days I want to report, including the ones that are Null Fields (which converts to "In Process") based on the expression written by TheAceMan1.

I think my explanation is a little confusing, but I hope I was able to explain it, and I wouldn't have figured it out without the help of the both of you.

Again, Thank you dhookom and TheAceMan1.

Thanks

 

[dhookom]

I changed the field type for Date Submitted to Ab into a Text Field

IMO, that was a horrible mistake. I would never change a data type to meet a single need like that. This change was worse than returning two different data types from an expression.

To me, it's simple [red]If you want to store a date, you use a date data type.[/red]

If you want the user to enter a date range, you do this through controls on a form.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

IIf(IsDate([Lease Tracking]![Date Submitted]), [Date Submitted], IIf([Lease Tracking]![Lease Consent Withdrawn]=0, "InProcess", "Withdrawn"))

...the text in bold converts the Date Submitted to Ab into a date format in case it wasn't formatted that way..

Not so! IsDate merely returns TRUE or FALSE. No CONVERSION results!!!

I wholeheartedly and empahtically agree with Duane's statement. "If you want to store a date, you use a date data type." Mixing data types in a column is a really bad, idea and it is NOT a best and accepted practice.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[iuianj07]

Hmmm, is that so? Well I'll try to work around it again, I just had to think or improvise a way for the query to run correctly since the deadline for this report is today (this is a weekly report)

Thanks again guys..