Reservation System Using Microsoft Access

[Steven TK]

Hi everyone,

I wonder who can help me on the filter the Start Date and End Date. I still cannot manage to filter it.

Eg. When the user click the StartDate(combo Box as 16/7/07), the Start Date(textbox should show only 16/7/07) and this also apply to the EndDate.

Private Sub cboStartDate_Cl ick()

'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.

'************** *************** *************** ***** *************** *******
'Look at each search box, and build up the criteria string from the non-blank ones.
'************** *************** *************** ***** *************** *******
'Text field example. Use quotes around the value in the string.

If Not IsNull(Me.cboSt artDate) Then
strWhere = strWhere & "(([StartDate] = '" & Me.cboStartDate & "')) OR "
End If


'************** *************** *************** ***** *************** ******
'Chop off the trailing " AND ", and use the string as the form's Filter.
'************** *************** *************** ***** *************** *******
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 4
If lngLen <= 0 Then 'Nah: there was nothing in the string.

MsgBox "No criteria", vbInformation, "Nothing to do."

Else 'Yep: there is something there, so remove the " Or " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cboEndDate_Clic k()

'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.

'************** *************** *************** ***** *************** *******
'Look at each search box, and build up the criteria string from the non-blank ones.
'************** *************** *************** ***** *************** *******
'Text field example. Use quotes around the value in the string.

If Not IsNull(Me.cboEn dDate) Then
strWhere = strWhere & "(([EndDate] = '" & Me.cboEndDate & "')) OR "
End If


'************** *************** *************** ***** *************** ******
'Chop off the trailing " AND ", and use the string as the form's Filter.
'************** *************** *************** ***** *************** *******
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 4
If lngLen <= 0 Then 'Nah: there was nothing in the string.

MsgBox "No criteria", vbInformation, "Nothing to do."

Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

[nairda]

Hi everyone,

I wonder who can help me on the filter the Start Date and End Date. I still cannot manage to filter it.

Eg. When the user click the StartDate(combo Box as 16/7/07), the Start Date(textbox should show only 16/7/07) and this also apply to the EndDate.

Private Sub cboStartDate_Cl ick()

'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.

'************** *************** *************** ***** *************** *******
'Look at each search box, and build up the criteria string from the non-blank ones.
'************** *************** *************** ***** *************** *******
'Text field example. Use quotes around the value in the string.

If Not IsNull(Me.cboSt artDate) Then
strWhere = strWhere & "(([StartDate] = '" & Me.cboStartDate & "')) OR "
End If


'************** *************** *************** ***** *************** ******
'Chop off the trailing " AND ", and use the string as the form's Filter.
'************** *************** *************** ***** *************** *******
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 4
If lngLen <= 0 Then 'Nah: there was nothing in the string.

MsgBox "No criteria", vbInformation, "Nothing to do."

Else 'Yep: there is something there, so remove the " Or " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cboEndDate_Clic k()

'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.

'************** *************** *************** ***** *************** *******
'Look at each search box, and build up the criteria string from the non-blank ones.
'************** *************** *************** ***** *************** *******
'Text field example. Use quotes around the value in the string.

If Not IsNull(Me.cboEn dDate) Then
strWhere = strWhere & "(([EndDate] = '" & Me.cboEndDate & "')) OR "
End If


'************** *************** *************** ***** *************** ******
'Chop off the trailing " AND ", and use the string as the form's Filter.
'************** *************** *************** ***** *************** *******
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 4
If lngLen <= 0 Then 'Nah: there was nothing in the string.

MsgBox "No criteria", vbInformation, "Nothing to do."

Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Hi, Steven

I'm not sure about what you want to make here, but I think you want to filter datas in your database using START DATE criteria nad END DATE criteria.
If this's right, I hope these codes can give you a hint about how to filter your data based on a period of time:

1. Instead of using comboboxes as your criteria input, it's easier to use DateTime Picker. (Project > Components > Microsoft Windows Common Control-2 6)

2. Use DT Picker to replace your combobox.

3. Use this SQL code:

"SELECT * FROM GUESTLIST WHERE Date>=#" & DTPicker1.Value & "# AND Date<=#" & DTPicker2.Value & "#"

4.You can change >= to just > and <= to < if you do not want to include the dates

FYI, I have a quite same problem a few days ago and have been solved by the forum. You can check the tread with header: "show report sort by period of time" on this forum.

Nairda

[Steven TK]

Hi, Steven

I'm not sure about what you want to make here, but I think you want to filter datas in your database using START DATE criteria nad END DATE criteria.
If this's right, I hope these codes can give you a hint about how to filter your data based on a period of time:

1. Instead of using comboboxes as your criteria input, it's easier to use DateTime Picker. (Project > Components > Microsoft Windows Common Control-2 6)

2. Use DT Picker to replace your combobox.

3. Use this SQL code:

"SELECT * FROM GUESTLIST WHERE Date>=#" & DTPicker1.Value & "# AND Date<=#" & DTPicker2.Value & "#"

4.You can change >= to just > and <= to < if you do not want to include the dates

FYI, I have a quite same problem a few days ago and have been solved by the forum. You can check the tread with header: "show report sort by period of time" on this forum.

Nairda

Hi Nairda,

Where do you get the Date Time Picker Control in microsoft access

[nairda]

Hi Nairda,

Where do you get the Date Time Picker Control in microsoft access

Hi,
Not in Ms.Access. Date Time Picker is a VB Component.
You can find it in Menu Project > Components > give a check sign on Microsoft Windows Common Controls-2.6.0. And then you can put DT Picker from your toolbox to your VB form.
You can use it to subtitute your combobox if you wanted to make a date list.
Give it a try.

Nairda

[Steven TK]

Hi,
Not in Ms.Access. Date Time Picker is a VB Component.
You can find it in Menu Project > Components > give a check sign on Microsoft Windows Common Controls-2.6.0. And then you can put DT Picker from your toolbox to your VB form.
You can use it to subtitute your combobox if you wanted to make a date list.
Give it a try.

Nairda

Hi Nairda, i try go to the component in the microsoft visual basic. I find only the microsoft window common control version 6 (SP 6) but i can't get the date picker as u say after i check the microsoft window common control version 6 (SP 6).

[nairda]

Hi Nairda, i try go to the component in the microsoft visual basic. I find only the microsoft window common control version 6 (SP 6) but i can't get the date picker as u say after i check the microsoft window common control version 6 (SP 6).

That's right, you can't find DT Picker if you selected Windows Common Control 6. You have to choose Windows Common Control 2.6.0 (SP4). I think the problem is we have a different version of VB or its Service Pack.
FYI, I used Visual Basic 6.0

In case you can't find DT Picker, you can still use your comboboxes, asl long as you use the same date format between your combo boxes and your database.
So if you use "mm dd yyyy" date format in your database, you have to add the same data format in your combo boxes.
The SQL code is the same (You just need to change DTPicker1.value with combo1.text)
The difference is you have to input many date in your combobox list meanwhile DT Picker has already give a "calendar looks" for you. You just pick the date you wanted.

Nairda

[Steven TK]

That's right, you can't find DT Picker if you selected Windows Common Control 6. You have to choose Windows Common Control 2.6.0 (SP4). I think the problem is we have a different version of VB or its Service Pack.
FYI, I used Visual Basic 6.0

In case you can't find DT Picker, you can still use your comboboxes, asl long as you use the same date format between your combo boxes and your database.
So if you use "mm dd yyyy" date format in your database, you have to add the same data format in your combo boxes.
The SQL code is the same (You just need to change DTPicker1.value with combo1.text)
The difference is you have to input many date in your combobox list meanwhile DT Picker has already give a "calendar looks" for you. You just pick the date you wanted.

Nairda

Hi Nairda,

I have a calendar control, 2 comboBox Date (1 is for the Start date and the other one is the End date). I want the user to choose according to their start date and end date by click the date they want in the calendar. Then the textbox will filter the date according to their needs

[nairda]

Hi Nairda,

I have a calendar control, 2 comboBox Date (1 is for the Start date and the other one is the End date). I want the user to choose according to their start date and end date by click the date they want in the calendar. Then the textbox will filter the date according to their needs

OK, I think you can do as I write before. Just be careful with your date format.

Nairda

[hariharanmca]

Hi Nairda,

I have a calendar control, 2 comboBox Date (1 is for the Start date and the other one is the End date). I want the user to choose according to their start date and end date by click the date they want in the calendar. Then the textbox will filter the date according to their needs

This is for MS Access :

"SELECT * FROM GUESTLIST WHERE Date>=#" & Format(DTPicker1.Value,"MM/dd/yyyy") & "# AND Date<=#" & Format(DTPicker1.Value,"MM/dd/yyyy")  & "#"

This is for SQL Server :

"SELECT * FROM GUESTLIST WHERE Date>= ' " & Format(DTPicker1.Value,"MM/dd/yyyy") & " ' AND Date<= ' " & Format(DTPicker1.Value,"MM/dd/yyyy")  & " ' "

Make sure that both date format should be Month/Day/Year format

other wise it will select the corresponding date format date only

[Steven TK]

This is for MS Access :

"SELECT * FROM GUESTLIST WHERE Date>=#" & Format(DTPicker1.Value,"MM/dd/yyyy") & "# AND Date<=#" & Format(DTPicker1.Value,"MM/dd/yyyy")  & "#"

This is for SQL Server :

"SELECT * FROM GUESTLIST WHERE Date>= ' " & Format(DTPicker1.Value,"MM/dd/yyyy") & " ' AND Date<= ' " & Format(DTPicker1.Value,"MM/dd/yyyy")  & " ' "

Make sure that both date format should be Month/Day/Year format

other wise it will select the corresponding date format date only

Sorry Nairda,

I wonder where you put this code in??

I am newbie for MS Access.. Maybe u tell me the procedure that could be better.. Sorry to trouble u.