Multiple Date Records

[Bryan Hughes]

Hello,

I am trying to write a query that will retrieve all Dates in a single field
using month criteria.

This part I am fine with.

The problem is that there can be 0 - 3 dates depending on the month.

I need to put each date retrieved into a unbound textbox.

Date1 = txtDate1
Date2 = txtDate2
Date3 = txtDate3

How should I write this to make it work?

-TFTH

-Bryan

 

[Danny J. Lesandrini]

SELECT Month([MyDateField]) AS MonthCalc, [MyDateField]
FROM MyTable
WHERE MonthCalc = 12
ORDER BY [MyDateField]

 

[[MVP] S.Clark]

What is the structure of the table holding these dates?


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Bryan Hughes]

Steve,

tblMyTable
[HCIDS] AutoNum PK
[HDate] Date/Time
[HNum] Integer
[HType] Text

Here is the query I have already written to return single date
****************************************
strSQL = "SELECT HDate "
strSQL = strSQL & "FROM tblCalendar_Year_Work_Holidays "
strSQL = strSQL & "WHERE Month([HDate])=" & intMyMonth & " "
strSQL = strSQL & "GROUP BY HDate;"

Set dbs = CurrentDb()
Set rst1 = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

With rst1
If Not .EOF Then
dtmHoliday = !HDate
Me.txtHoliday1 = dtmHoliday
Else
MsgBox "Holiday not found"
End If
End With
****************************************************
How do I handle several returned dates?

-TFTH
-Bryan

 

[[MVP] S.Clark]

Now explain more about txtDate1, 2, & 3, as I don't understand how returning
one date field from the query is supposed to translate to 3 text boxes.

Steve,

tblMyTable
[HCIDS] AutoNum PK
[HDate] Date/Time
[HNum] Integer
[HType] Text

Here is the query I have already written to return single date
****************************************
strSQL = "SELECT HDate "
strSQL = strSQL & "FROM tblCalendar_Year_Work_Holidays "
strSQL = strSQL & "WHERE Month([HDate])=" & intMyMonth & " "
strSQL = strSQL & "GROUP BY HDate;"

Set dbs = CurrentDb()
Set rst1 = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

With rst1
If Not .EOF Then
dtmHoliday = !HDate
Me.txtHoliday1 = dtmHoliday
Else
MsgBox "Holiday not found"
End If
End With
****************************************************
How do I handle several returned dates?

-TFTH
-Bryan

What is the structure of the table holding these dates?


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Bryan Hughes]

Steve,

In the table there can be up to three dates for a given month.

I need to get the dates for that month and put each of them in an unbound
textbox.

What would be the best way to do this?

-Bryan

Now explain more about txtDate1, 2, & 3, as I don't understand how returning
one date field from the query is supposed to translate to 3 text boxes.

Steve,

tblMyTable
[HCIDS] AutoNum PK
[HDate] Date/Time
[HNum] Integer
[HType] Text

Here is the query I have already written to return single date
****************************************
strSQL = "SELECT HDate "
strSQL = strSQL & "FROM tblCalendar_Year_Work_Holidays "
strSQL = strSQL & "WHERE Month([HDate])=" & intMyMonth & " "
strSQL = strSQL & "GROUP BY HDate;"

Set dbs = CurrentDb()
Set rst1 = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

With rst1
If Not .EOF Then
dtmHoliday = !HDate
Me.txtHoliday1 = dtmHoliday
Else
MsgBox "Holiday not found"
End If
End With
****************************************************
How do I handle several returned dates?

-TFTH
-Bryan

What is the structure of the table holding these dates?


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hello,

I am trying to write a query that will retrieve all Dates in a single
field
using month criteria.

This part I am fine with.

The problem is that there can be 0 - 3 dates depending on the month.

I need to put each date retrieved into a unbound textbox.

Date1 = txtDate1
Date2 = txtDate2
Date3 = txtDate3

How should I write this to make it work?

-TFTH

-Bryan

 

[[MVP] S.Clark]

And how is this supposed to work in relation to the desired 3 textboxes on
the form?

Steve,

tblMyTable
[HCIDS] AutoNum PK
[HDate] Date/Time
[HNum] Integer
[HType] Text

Here is the query I have already written to return single date
****************************************
strSQL = "SELECT HDate "
strSQL = strSQL & "FROM tblCalendar_Year_Work_Holidays "
strSQL = strSQL & "WHERE Month([HDate])=" & intMyMonth & " "
strSQL = strSQL & "GROUP BY HDate;"

Set dbs = CurrentDb()
Set rst1 = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

With rst1
If Not .EOF Then
dtmHoliday = !HDate
Me.txtHoliday1 = dtmHoliday
Else
MsgBox "Holiday not found"
End If
End With
****************************************************
How do I handle several returned dates?

-TFTH
-Bryan

What is the structure of the table holding these dates?


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-