Filter date for report from a popup form

[Rich]

Returning/using dates in strings esp. outside of the US is not easy, you have to use the # delimiters and the Format function. Your code is filtering for a text value and there is no reference to the date field you also want to filter on in your code.

 

[Mike375]

Returning/using dates in strings esp. outside of the US is not easy, you have to use the # delimiters and the Format function. Your code is filtering for a text value and there is no reference to the date field you also want to filter on in your code.

Rich,

In my Access 95 if I put >23/3/48 in a query criteria it automatically changes it to >#23/03/1948#

 

[Mike375]

btw, the earlier code (pasted on page #1) was for the button ... and it works except for the date field ... so I will take your comments onboard ... thanks

ozlander

If you are searching for dates that are perhaps based on a date in a field plus so many days or minus so many days then you can place an unbound text box on your form and for its data sourse just have =([MyDateField]+30) and that for example will show the result. Your query criteria can then reference that textbox.

Mike

 

[ozlander]

Hey Rich ... do you mean there is hope ? Could one provide an OR statement in the middle of that code or something else?

And Mike, my db is a learning tool at the moment for me ... I just really wanted to know if it would be possible to do both ... I have the queries set up individually for the report (as per instruction) but I still reckon it should be possible to combine them. In the meantime, I am searching, searching, searching and reading, reading, reading while learning, learn... ing ... a long, long way to go ... I am a real rookie (old though)

 

[ozlander]

The dates queried come up in the box on the form ... so no problem there

It was the code behind the button to set the filter that won't accept the dates

 

[Rich]

Rich,

In my Access 95 if I put >23/3/48 in a query criteria it automatically changes it to >#23/03/1948#

That's because the query sees the field as a valid date field. Oz is building the filter from a string, it's not the same. Whilst it is possible to do this directly in the query grid it needs a combination of Is Null Or etc. and the parameter boxes will still pop up

 

[ozlander]

Rich

"... a combination of Is Null Or etc. and the parameter boxes will still pop up ..."

Is Null Or etc ... could you please explain further?

 

[Mike375]

That's because the query sees the field as a valid date field. Oz is building the filter from a string, it's not the same. Whilst it is possible to do this directly in the query grid it needs a combination of Is Null Or etc. and the parameter boxes will still pop up

In my data base I have references in macro conditrions or Where, although they reference a date field

([Da]<(Date()-[Days]) Or [Ringdate1] Is Null) And (IIf([ActivityDate] Is Null,([ActionDate]<Time() And [ActionDateNo]>([TimeExp]-([Hours]*3600))) Or [Actiondate] Is Null,[ActionDate]<Time() And [Activitydate]>Time())) And [S4] Like 10

But I also have unbound text boxes where a date is entered in each and then a setvalue transfers those dates to unbound text bases on another form. I just checked those text boxes and for Format I have it left blank.

I think I have some macros with either Conditions or Where with dates typed in but I am not 100% sure of that.

Mike

 

[Rich]

I wouldn't advise the use of macros at all, there's no error handling, and Like 10 would be better as =10

 

[ozlander]

Hey, guys ... remember me?

reading thus far ...

1 to do nothing if control is Null, test with IsNull()
2 to specify an assumption to use Null, use Nz() ..... (and I thought that stood for New Zealand)
3 to handle the Null, assign the control's value to a variant

ok ... do I alter the control as a first option so I can apply point #2 followed by #3 ... or have I missed the boat completely?

I am trying to learn here ... and I promise I won't give up my day job

 

[Mike375]

I wouldn't advise the use of macros at all, there's no error handling, and Like 10 would be better as =10

I have some macros with Life and some with = for numbers. No real reasson as I think it was the time when I made them.

Yes, I have noticed on these forums that just about everyone is anti macro. My coding is limited to opening Word and then iserting Access field data into Bookmarks.

My data base has over 2000 macros but the only time I have had errors is when I have first made some macros and that has been a case of typos on field referencinhg etc and then the big HALT comes up.

Mike

 

[ozlander]

2 000 ... wow!!!!

Btw, mike ... I do realise that I could do it in a different manner, however, I want to attempt to understand the filtering process and perhaps its limits

And I am serious ... I won't be giving up my day job!!!!!

Oz

 

[Mike375]

2 000 ... wow!!!!

Btw, mike ... I do realise that I could do it in a different manner, however, I want to attempt to understand the filtering process and perhaps its limits

And I am serious ... I won't be giving up my day job!!!!!

Oz

My advice (which could be wrong ) is to first get the thing working. That way you have

1) The thing working, which is always a help

2) A clear picture of what has to be done if you want to do it another way. When you have a working exampe that satisfies the most important need. It then means you can work on a copy of your data base without having the pressure that comes when the stuff does not work.


Mike

 

[ozlander]

The original db is working ... I was experimenting with something I knew nothing about ... that's why I have asked just my second question on the forum

I don't suppose I should ever ask my third question ...

Oz

 

[Mike375]

The original db is working ... I was experimenting with something I knew nothing about ... that's why I have asked just my second question on the forum

I don't suppose I should ever ask my third question ...

Oz

I am retiring from data base activity for the night. I will watch the channel Nine news and see if the world is still there

Mike

 

[ozlander]

and I promise I won't give up my day job