date function

[geraldk]

How do I find a date in if statement.



I need to search in a list box that is in the second column and need to find dates that are between July 1, 2011 and June 30, 2012.

[Robeen]

Where are you wanting to use your If statement?
Are you wanting your List to contain only dates between a certain range?

Can you be more specific?

[Robeen]

If you want your ListBox to only contain dates within a certain range - you can try something like this in the Property Sheet -> Data Tab -> Row Source of the List Box:

 
SELECT DISTINCT [TableName].[DateField] FROM [TableName] 
WHERE [DateField] > #9/01/2011# and [DateField] < #10/01/2011#
ORDER BY [DateField];

I hope this helps!

[geraldk]

Where are you wanting to use your If statement?
Are you wanting your List to contain only dates between a certain range?

Can you be more specific?

What I want to get is the date range from my if statement and display value to a text box.

For an example:

Values from List Box
Vacation Day June 1, 2010
Vacation Day July 2, 2010
Vacation Day August 20, 2010
Vacation Day September 2, 2010

Since our Vacation Period is from July 1 to June 30 the following year

I want to select on the ones from July 1 to June 30, 2012

So, I only need to select July 2, Aug 20 and September 2 to give me a value of three days.

What I want to do once I have the IF statment working is to get the year value from a text box in case I want to find out how many vacation days someone took in a previous year.

Thanks,

[Robeen]

Where do the dates in the List box come from?
Are they hard-coded into the List Box or are they in a Table?

Why do you need an 'If statement'?

Is your statement going to execute when the Form Loads - or are you going to put it behind a button that you click?

If I understand correctly, you want to calculate how many of the vacation days that are in your List Box occur between July 1 of one year and June 30 of the following year - is that right?

[geraldk]

So my SQL statement in my List box to find VacDate ID (1=Paid)

SELECT Count(vacid) AS VAC
FROM tblempvac
WHERE (((tblempvac.vacid)=1) AND ((tblempvac.EMPVACDate)>=[forms]![frmVacation]![txtDate1] And (tblempvac.EMPVACDate)<=[forms]![frmVacation]![txtDate2]));

Then code for the form when it's loading:

Me.txtVacYear = Format(Now(), "yyyy")
Dim date1 As String
Dim date2 As String
date1 = "07/01/" & Me.txtVacYear
date2 = "06/30/" & Me.txtVacYear + 1
Me.txtDate1.value = "#" & date1 & "#"
Me.txtDate2.value = "#" & date2 & "#"

Dim value As Integer
Dim i As Integer
Dim str As String

With ListBox
For i = 0 To Me.lstVacDays.ListCount
str = Me.lstVacDays.Column(0, i) & str
Next i
End With
value = Forms!frmEmployee!EmpVacation - str
Me.txtVacDay.value = value
Me.txtVacDay.Locked = True

When I run the form my list to find the VACID it comes up zero.

When I replace the forms!frmVacation!txtDate1 with #07/01/2011# and ...!txtDate2 with #06/30/2012# my query will give me the correct value that is from list.

Not sure how to pass down txtDate1 and date2 to sql statement.

[Robeen]

Try this for your SQL:

 
WHERE (((tblempvac.vacid)=1) AND 
((tblempvac.EMPVACDate)>= #[forms]![frmVacation]![txtDate1]# And (tblempvac.EMPVACDate)<=#[forms]![frmVacation]![txtDate2]#));

I'm not sure this will work when you open the Form - because your two text boxes will be empty to begin with - right?

You might try using this statement after you have supplied values to both your text boxes:

 
Me![listboxname].Requery

Let me know if this works - I've never done this before but it sounds do-able!

[geraldk]

The Text boxes for the Dates are loaded up with Form Load()

[Robeen]

Did you try the SQL I recommended? Did it work?

[geraldk]

Did you try the SQL I recommended? Did it work?

Did not work. The list box came up blank.

[Robeen]

I was not able to do EXACTLY what you are trying - but I was able to get the result you want.

My ListBox is named List25.
I created a command button named cmdUpdateListBox and put this code behind it.

 
Private Sub cmdUpdateListBox_Click()
Dim StartDate, EndDate As Date
 
Me.txtStartDate.SetFocus
StartDate = Me.txtStartDate.Text
 
Me.txtEndDate.SetFocus
EndDate = Me.txtEndDate.Text
 
Me.List25.RowSource = "SELECT DISTINCT [YourTableName].[DateField] FROM [YourTableName] WHERE [DateField] > #" & StartDate & "# and [DateField] < #" & EndDate & "# ORDER BY [DateField]; "
 
Me.List25.Requery
 
End Sub

When I click the command button, the list box will be modified to only display dates within the date range in the two text boxes.

I'm thinking you can put the above code somewhere after the Form Load event and as long as there are dates in the two text boxes, it will still update the List Box.

Let me know how it goes!

[Robeen]

P.S.

I put this in the Form Load Event of the Form and it did what I think you need.

 
Private Sub Form_Load()
Dim StartDate, EndDate As Date
 
Me.txtStartDate.SetFocus
Me.txtStartDate.Text = "9/01/2011"
StartDate = Me.txtStartDate.Text
 
Me.txtEndDate.SetFocus
Me.txtEndDate.Text = "9/15/2011"
EndDate = Me.txtEndDate.Text
 
Me.List25.RowSource = "SELECT DISTINCT [Copy Of 12-09-2011].[Report Date] FROM [Copy Of 12-09-2011] WHERE [Report Date] > #" & StartDate & "# and [Report Date] < #" & EndDate & "# ORDER BY [Report Date]; "
 
Me.List25.Requery
 
End Sub

Let me know if that helps.

[geraldk]

That worked.

Thank you so much!

[Robeen]

You're welcome.

Mark it as solved [From the Thread Tools drop down at the top of the page] - and click on the scales on my post if you would like to give me a boost!!