Linking Dates together!

[broodis]

HI All,

Now I'm trying to link the dates for 3 tables tblDate, tblStore and tblRepair. So far I've tried this approach:

tbl Date tblRepair

RefDate Date Date RepairID PartsID Repaired TechID
1 1/26/2005 1 1 cable 3 John
2 1/27/2005 1 2 board 6 mike
3 1/28/2005 2 3 wiring 1 mark

tblStore
Date BlcAt(8AM) PartsID Unrepaired Repaired BlcAt(4PM)
1 2 cable 4 3 1
1 7 board 6 6 6
2 3 wiring 2 1 7


The whole database works well, but when I want to print out the reports for a specific date it will only read the RefDate. Is there a way where I can make it read the field Date? Here is the code for looking for the specific date.

Private Sub cmdtoday_Click()
'Sets the Date From and Date To text boxes
'to Today's Date

Me!txtdatefrom = Date
Me!txtDateTo = Date

End Sub

Private Sub cmdweek_Click()
'Sets the Date From and Date To text boxes
'to show complete working week (Mon - Fri)

Dim today

today = Weekday(Date)

Me!txtdatefrom = DateAdd("d", (today * -1) + 2, Date)
Me!txtDateTo = DateAdd("d", 6 - today, Date)

End Sub

Private Sub cmdmonth_Click()
'Sets the Date From and Date To text boxes
'to show complete month (from start to end of current month)

Me!txtdatefrom = CDate("01/" & Month(Date) & "/" & Year(Date))
Me!txtDateTo = DateAdd("d", -1, DateAdd("m", 1, Me!txtdatefrom))

End Sub

Private Sub cmdyear_Click()
'Sets the Date From and Date To text boxes
'to show complete current year

Me!txtdatefrom = CDate("01/01/" & Year(Date))
Me!txtDateTo = DateAdd("d", -1, DateAdd("yyyy", 1, Me!txtdatefrom))

End Sub
Private Sub cmdReport_Click()
On Error GoTo Err_cmdReport_Click

Dim stDocName As String

stDocName = "rptAll"

'Check values are entered into Date From and Date To text boxes
'if so run report or cancel request

If Len(Me.txtdatefrom & vbNullString) = 0 Or Len(Me.txtDateTo & vbNullString) = 0 Then
MsgBox "Please ensure that a report date range is entered into the form", _
vbInformation, "Required Data..."
Exit Sub
Else
DoCmd.OpenReport stDocName, acPreview
End If
Exit_cmdReport_Click:
Exit Sub

Err_cmdReport_Click:
MsgBox Err.Description
Resume Exit_cmdReport_Click

End Sub

 

[dhookom]

Without looking at your posting too far, do you realize that Date is a function and should never be used as a field or control name? If you use "Date" in code, do you expect Access to understand if you mean the field, control, or function value?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Something like this ?
SELECT *
FROM (tblDate D
LEFT JOIN tblRepair R ON A.RefDate=R.Date)
LEFT JOIN tblStore S ON A.RefDate=S.Date
WHERE D.Date Between [Forms]![name of form]![txtDateFrom] And [Forms]![name of form]![txtDateTo]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[broodis]

To dhookom,

Is there another way to link the dates without creating a table for dates? Thanks


To PHV,

Where do I write the code in?Thanks

 

[PHV]

Doesn't your report have a RecordSource property ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[broodis]

SELECT *
FROM (tblDate D
LEFT JOIN tblRepair R ON A.RefDate=R.Date)
LEFT JOIN tblStore S ON A.RefDate=S.Date
WHERE D.Date Between [Forms]![name of form]![txtDateFrom] And [Forms]![name of form]![txtDateTo]

Just want to know whats :

D , R ,A, and S.

Sorry about that. Thanks PHV

 

[PHV]

Oops, sorry for the typo:
SELECT *
FROM (tblDate D
LEFT JOIN tblRepair R ON D.RefDate=R.Date)
LEFT JOIN tblStore S ON D.RefDate=S.Date
WHERE D.Date Between [Forms]![name of form]![txtDateFrom] And [Forms]![name of form]![txtDateTo]

D, R and S are alias for table names.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[broodis]

Hi PHV,

Still can't get the thing to work. Is there a way I can send you my database so that you can look for it? Or do you know how to open a report using a combo box with the record source tblDate.RefDate??? Thanks!

 

[PHV]

Still can't get the thing to work
Does the query by itself works ?
In query design view create a brand new query, go to the SQL view pane, copy/paste my last code.
Be sure the form is open and the 2 dates populated.
Choose the data view of the query and let me know if it worked.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[broodis]

Hi PHV,

I'm really useless at SQL. I've tired editing it several times but I can't get it to work. This is my current SQL for my report:

SELECT tblStore.RefDate, tblStore.StoreID, tblStore.PartsID, tblStore.[BalanceAtStore(8AM)], tblStore.BadPartsReceived, tblRepaired.TechID, tblRepaired.Repaired, tblStore.Unrepaired, tblStore.[BalanceAtStore(4PM)], [Repaired]+[BalanceAtStore(4PM)] AS [Total Repaired + BalanceAtStore(4PM)]
FROM tblRepaired INNER JOIN tblStore ON (tblRepaired.RefDate = tblStore.RefDate) AND (tblRepaired.PartsID = tblStore.PartsID)
WHERE (((tblStore.RefDate)>=[forms]![frmPop]![txtDateFrom] And (tblStore.RefDate)<=[forms]![frmPop]![txtDateTo]));

 

[broodis]

Could you help me modify it PHV?? Thanks. Really appreciate it.

 

[PHV]

I can't get it to work
What happens ? Error message ? Unexpected result ?
Please repost your new tables schema with some input samples and expected result.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244