Date Submitted Query...

[iuianj07]

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]

How are ya iuianj07 . . .

Try this:

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

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[iuianj07]

Thanks! it worked perfectly!! Can you please explain to me the whole query?

THanks,

I really appreciate it!

 

[TheAceMan1]

iuianj07 . . .

There is [blue]logic[/blue] as you displayed in your post origination ... and there is [blue]logic flow[/blue] as I displayed to you! Any [blue]logic[/blue] has to have [purple]proper flow![/purple]

I looked at your code and determined that [Date Submitted] has priority ... hence the [blue]1st IIf statement.[/blue]

If [Date Submitted] failed (a null in your case) then 2nd priority is up to [Lease Consent Withdrawn]. This is taken care of by the false part of the 1st IIf which which is another IIf that checks [Lease Consent Withdrawn]. I hope you see this! If not let me know.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

iuianj07 . . .

BTW: Welcome to [blue]Tek-Tips![/blue] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue] [blue]Its Worthy Reading![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[iuianj07]

Hello TheAceMan1,

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 but all to no avail, I hope you could read this and help me out. I really appreciate it.

Thank you very much

 

[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,

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 but all to no avail, I hope you could read this and help me out. I really appreciate it.

Thank you very much