qtr and open form

[alpapak]

hi

i have a continious form with the fields

year || Qtr 1 || Qtr 2 || Qtr3 | etc.. || Qtr12
----------------------------------------------------------
2000 4 5 2 6
2001 3 3 6 2
2002 6 4 3 3

the Qtr are the months, so
Qtr 1 = January Qtr 2 = ... Qtr 12 = December

the numbers are customers that visit the office that month

i want example:
to double click on Qtr 1 / 2001 which is 3 and open form customer and see only the customers that visit the office from 1/1/2001 until 31/1/2001


i manage to make it work , with this code:

Option Compare Database
Option Explicit

Private Sub Qtr_1_DblClick(Cancel As Integer)
Dim YearTake As String
YearTake = Me.Year
DoCmd.OpenForm "customers", , , "[customers_date_visit] Between #" & "1/1/" & YearTake & "# And #" & "31/1/" & YearTake & "#"
End Sub

Private Sub Qtr_2_DblClick(Cancel As Integer)
Dim YearTake As String
YearTake = Me.Year
DoCmd.OpenForm "customers", , , "[customers_date_visit] Between #" & "1/2/" & YearTake & "# And #" & "31/2/" & YearTake & "#"
End Sub

Private Sub Qtr_3_DblClick(Cancel As Integer)
Dim YearTake As String
YearTake = Me.Year
DoCmd.OpenForm "customers", , , "[customers_date_visit] Between #" & "1/3/" & YearTake & "# And #" & "31/3/" & YearTake & "#"
End Sub

Private Sub Qtr_4_DblClick(Cancel As Integer)
Dim YearTake As String
YearTake = Me.Year
DoCmd.OpenForm "customers", , , "[customers_date_visit] Between #" & "1/4/" & YearTake & "# And #" & "31/4/" & YearTake & "#"
End Sub

etc Qtr...


BUT
i face :
1)the qtr 2 which is the second month of the year (February) has every year different number of days.
2)when i click qtr 3 it brings all the records from qtr 1 + qtr 2 + qtr 3
the same does when i click qtr 4 , qtr 5, ...qtr 12
it brings the sum of all the previous months.???
the only one that works fine is qtr 1 which is the first month...

 

[RuralGuy]

Sorry but you will need to use dates in US format. Return Dates in US #mm/dd/yyyy# format

 

[alpapak]

thxs for the reply

one question ?
all my db uses dd/mm/yyyy format.
is it going to harm other forms?
or is it just for this problem.

 

[RuralGuy]

The date is *always* stored in the same manner. It is just that when you search on the stored date you *must* use the US format. How you display it is strictly up to you.

 

[Moniker]

Just as a semantics sort of thing, each year has four quarters in it, each typically separated Jan-Mar, Apr-Jun, Jul-Sep, and Oct-Dec. Each quarter is often abbreviated as "Qtr". Since you use what the US considers a somewhat different date format (not that we can't read it, but it takes a second), maybe the abbreviation "Qtr" means something else in other parts and I'm just not aware of it. However, I've never seen "Qtr" used as anything but "Quarter", meaning those field names are potentially very confusing.

 

[alpapak]

i tried the following code:

Private Sub Qtr_3_DblClick(Cancel As Integer)
Dim YearTake As String
Dim StartDate As String
Dim EndDate As String
YearTake = Me.Year
StartDate = "1/3/" & Year
EndDate = "1/3/" & Year
DoCmd.OpenForm "frmCustomers", , , "[customers_date_visit] Between #" & Format(StartDate , "dd/mm/yyyy") & "# And #" & Format(EndDate , "dd/mm/yyyy") & "#"
End Sub

but still the same.
if i click Qtr_3 , open the frmCustomers and it sun Qtr_1 , Qtr_2 , Qtr_3
if i click qtr_5 , open the frmCustomers and it sun Qtr_1 , Qtr_2 , Qtr_3 , Qtr_4 , Qtr_5

when i tested the following it works , but why???

Private Sub Qtr_3_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmCustomers", , , "[customers_date_visit] Between #" & [Place the start date?] & "# And #" & [Place the end date?] & "#"
End Sub

it pop up two windows where the user write the dates.

-------------------

the form customersNew is made by the following query
query code:

TRANSFORM Count(([customersID])) AS customersNew
SELECT Year([customers_date_visit]) AS customersYear
FROM frmCustomers
GROUP BY Year([customers_date_visit])
ORDER BY Year([customers_date_visit]) DESC
PIVOT "Qtr " & DatePart("m",[customers_date_visit],1,0) In ("Qtr 1","Qtr 2","Qtr 3","Qtr 4","Qtr 5","Qtr 6","Qtr 7","Qtr 8","Qtr 9","Qtr 10","Qtr 11","Qtr 12");

 

[alpapak]

error code '13

code:
Private Sub Qtr_4_DblClick(Cancel As Integer)
Dim YearTake As String
Dim StartDate As String
Dim EndDate As String
YearTake = Me.Year
StartDate= "1/4/" & YearTake
EndDate = "1/5/" & YearTake
DoCmd.OpenForm "customers", , , "[customers_date_visit] >= #" & Format(StartDate, "mm / dd / yyyy") & "#" And "[customers_date_visit] < #" & Format(EndDate , "mm / dd / yyyy") & "#"
End Sub

 

[RuralGuy]

Try using the technique described in the link I posted.

 

[alpapak]

DoCmd.OpenForm "customers", , , "[customers_date_visit] Between #" & Format(StartDate, "mm / dd / yyyy") & "#" And #" & Format(EndDate , "mm / dd / yyyy") & "#"

i read the link and i made the code above which works great.
But it shows the first day of the next month
so i decide to change Between , And
to >= , <

 

[RuralGuy]

Thanks for posting back with your success.

 

[alpapak]

Read my last post

i want to change the Between , And to >= , <

 

[alpapak]

DoCmd.OpenForm "customers", , , "[customers_date_visit] >= #" & Format(StartDate, "mm / dd / yyyy") & "#" & "And" & "[customers_date_visit] < #" & Format(EndDate , "mm / dd / yyyy") & "#"



Ok!!!