Data Range on a Form~!~

[LaRockera02]

haha Good!~!~

So what do I do now?

 

[vbaInet]

I thought you already had this sorted from what was mentioned in post #14?

 

[LaRockera02]

No I don't, you lost me after you started to ask me all these questions lol I'm assuming you are reffering to this code:

DoCmd.OpenReport "SearchCallDat", acViewPreview,, "[ BETWEEN #" & Me.txtBegDate & "# AND #" & Me.txtEndDate & "#"

Where do you want me to add this?

 

[LaRockera02]

hey did you forget about me?

 

[vbaInet]

You have a button that opens the report right?

 

[LaRockera02]

CORRECT is a search button

 

[vbaInet]

So the code should go in the ON CLICK event of the button (Code Builder).

 

[LaRockera02]

YEY!!!! I had to change a few things but it worked. thanks you are amazing as usual

 

[vbaInet]

Good job LaRockera02!

 

[LaRockera02]

Hey vBaInet guess what!!! My boss came back with some other issues. What you gave me up till now works perefectly but I get a syntax error when I don't input a date.

We need to be able to do the following:

• Search on any field without dates
• Search on start date
• Search on end date
• Search on range of dates (this is what you helped me on)

I also noticed that when I hit the search button it doesn't capture the end date. For example: I may click on search between 10/11/2011 ti 10/14/2011 and it will give me the results only up to 10/13/2011.

I know you must hate me up to now lol but I'm learning a great deal from you and truly appreciate your help..

thanks

 

[vbaInet]

We need to be able to do the following:

• Search on any field without dates
• Search on start date
• Search on end date

These three basically mean that when both textboxes are empty you would like it to return all records?

When end date textbox is empty you would like it to show records based on start date. And vice versa?

 

[LaRockera02]

These three basically mean that when both textboxes are empty you would like it to return all records?

CORRECT

When end date textbox is empty you would like it to show records based on start date. And vice versa?

Umm If what you meant is when the end date is empty I want for it show all records beggining with the start date until the last record it can find. and if the start date is empty I want for it to provide me with all the records up to the end date (making sure records on that date are included)

 

[vbaInet]

You will need to write some more code.

* If Start Date is not a date, get the Min date. Else use Start Date
* If End Date is not a date, get the Max date. Else use End Date

Then put the value from above into the criteria.

 

[LaRockera02]

You must really have this high opinion of me if you think I can write code lol

I'm assuming it involves some sort of if else statement with the min & max functions? This is the best I can do, sorry :

If (Me.txtBegDate__txtStartDate) <> DATE Then
Function Min()
Else
Me.txtBegDate__txtStartDate
End If
 
If (Me.txtEndDate) <> DATE Then
Function Max()
Else
Me.txtEndDate
End If

 

[vbaInet]

I trust your abilities

I will give you some aircode. See what you can do with it:

Dim dteBeg As Date, dteEnd As Date

If IsDate(Me.txtBegDate) Then
    dteBeg = Me.txtBegDate
Else
    dteBeg = DMin(...)
End If

If IsDate(Me.txtEndDate) Then
     dteEnd = Me.txtEndDate
Else
     dteEnd = DMax(...)
End If


DoCmd.OpenReport ........ use dteBeg and dteEnd in the BETWEEN

... where DMin() will get the minimum date in the table and DMax() will get the maximum date in the table.

Here are DMin and DMax links to get you going:

http://www.techonthenet.com/access/functions/domain/dmin.php
http://www.techonthenet.com/access/functions/domain/dmax.php

 

[LaRockera02]

this is what I got until now:

Dim dteBeg As Date, dteEnd As Date
If IsDate(Me.txtBegDate__txtStartDate) Then
    dteBeg = Me.txtBegDate__txtStartDate
Else
    dteBeg = DMin("CreatedDate", "qrySearchCalDat", [COLOR=red][][/COLOR])
End If
If IsDate(Me.txtEndDate) Then
     dteEnd = Me.txtEndDate
Else
     dteEnd = DMax("CreatedDate", "qrySearchCalDat", [COLOR=red][][/COLOR])
End If
DoCmd.OpenReport "SearchCallDat", acViewPreview, , "[CreatedDate] BETWEEN #" & dteBeg & "# AND #" & dteEnd & "#"

Not sure what you meant by this so I left it blank above, what should I enter for criteria?

Then put the value from above into the criteria.

 

[vbaInet]

The 3rd argument is not required. Or is it?

You did exactly what I mentioned so somehow you understood what I meant

 

[LaRockera02]

haha yey for me!!! No the criteria is not required but I somehow thought it was needed in order to work because the BETWEN function does not work,it just gives me all the records.

 

[vbaInet]

It's giving you all the records when you enter dates into both txtBegDate and txtEndDate textboxes?

I'm hoping those textboxes are not bound?

 

[LaRockera02]

yes is giving me all the records when I entered a date for both.

Both are unbound textboxes