Queries

[Ecal]

Hello, I am trying to create a reciept database to log all my receipts and to let me know how much I spent for the month I have the query's set up just fine but as we all should know the months that have not come yet access is giving me a error when I go to run the vba script because the queries are not picking up anything. Let me post my vba code but before I do I have queries set for each month labeled qryJanTotal to qryDecTotal to give a count on for each transaction and the Total amount spent. Here is the code

Private Sub Form_Load()
Dim strMonth1 As Double
Dim strMonth2 As Double
Dim strMonth3 As Double
Dim strMonth4 As Double
Dim strMonth5 As Double
Dim strMonth6 As Double
Dim strMonth7 As Double
Dim strMonth8 As Double
Dim strMonth9 As Double
Dim strMonth10 As Double
Dim strMonth11 As Double
Dim strMonth12 As Double

strMonth1 = DLookup("SumOfAmount", "qryJanTotal")
Me.txtJanuary = strMonth1
strMonth2 = DLookup("SumOfAmount", "qryFebTotal")
Me.txtFebruary = strMonth2
strMonth3 = DLookup("SumOfAmount", "qryMarTotal")
Me.txtMarch = strMonth3
strMonth4 = DLookup("SumOfAmount", "qryAprTotal")
Me.txtApril = strMonth4
' strMonth5 = DLookup("SumOfAmount", "qryMayTotal")
' If strMonth5 = Null Then
' Me.txtMay = 0

' Else
' Me.txtMay = strMonth5
' End If

' strMonth6 = DLookup("SumOfAmount", "qryJunTotal")
'Me.txtJune = strMonth6
' strMonth7 = DLookup("SumOfAmount", "qryJulTotal")
' Me.txtJuly = strMonth7
' strMonth8 = DLookup("SumOfAmount", "qryAugTotal")
' Me.txtAugust = strMonth8
' strMonth9 = DLookup("SumOfAmount", "qrySeptTotal")
' Me.txtSeptember = strMonth9
' strMonth10 = DLookup("SumOfAmount", "qryOctTotal")
' Me.txtOctober = strMonth10
' strMonth11 = DLookup("SumOfAmount", "qryNovTotal")
' Me.txtNovember = strMonth11
' strMonth12 = DLookup("SumOfAmount", "qryDecTotal")
' Me.txtDecember = strMonth12

End Sub

I have not entered the Month of April receipts yet just to see if it is my code that is wrong or the way it was recieving the data. As you can see I commented much of it out to try and trouble shoot the code. I also tried to right an if statement to see if I can put parameters on the code but now I am at a loss I realize I could of used an array on this but I am still a little shaky on using them. On the If statement I tried to use Null to see if I can give it perameters but no luck.

 

[boblarson]

What is the SQL of your queries (just post one)? It is highly likely you can use a SINGLE query to get the monthly aggregated values and simplify things IMMENSELY.

 

[Ecal]

Ok here is one
SELECT Count(TblReceipts.ID) AS CountOfID, Sum(TblReceipts.Amount) AS SumOfAmount
FROM TblReceipts
HAVING ((([TblReceipts]![Purchase Date]) Between #4/1/2013# And #4/28/2013#));

 

[boblarson]

Okay, so you can create your single query by doing this:

Select Sum(IIf([PurchaseDate] Between #1/1/2013# And #1/31/2013#, 1, 0) As JanuaryCount, Sum(IIf([PurchaseDate] Between #1/1/2013# And #1/31/2013#, [TblReceipts].[Amount], 0) As JanuaryAmount,
Sum(IIf([PurchaseDate] Between #2/1/2013# And #2/28/2013#, 1, 0) As FebruaryCount, Sum(IIf([PurchaseDate] Between #2/1/2013# And #2/28/2013#, [TblReceipts].[Amount], 0) As FebruaryAmount,

...etc.
FROM TblReceipts


And I just gave you the hard coded dates but you can do it with DateSerial and make it more reusable and not have to go modify it each year.

 

[Ecal]

How do you make it more reusable with SerialDates and is there any way to do the same affects if you where to use VBA?

 

[boblarson]

How do you make it more reusable with SerialDates and is there any way to do the same affects if you where to use VBA?

What do you mean as far as VBA goes?

So, for DateSerial

Between DateSerial(Year(Date()), 1, 1) And DateSerial(Year(Date()), 1, 31)

as an example.

 

[Ecal]

Okay, so you can create your single query by doing this:

Select Sum(IIf([PurchaseDate] Between #1/1/2013# And #1/31/2013#, 1, 0) As JanuaryCount, Sum(IIf([PurchaseDate] Between #1/1/2013# And #1/31/2013#, [TblReceipts].[Amount], 0) As JanuaryAmount,
Sum(IIf([PurchaseDate] Between #2/1/2013# And #2/28/2013#, 1, 0) As FebruaryCount, Sum(IIf([PurchaseDate] Between #2/1/2013# And #2/28/2013#, [TblReceipts].[Amount], 0) As FebruaryAmount,

...etc.
FROM TblReceipts


And I just gave you the hard coded dates but you can do it with DateSerial and make it more reusable and not have to go modify it each year.

So How would I make it more reusable using VBA or SQL Kind of new to SQL.

 

[Ecal]

So how would I do it with DateSerial and make it more reusable. Kind of new to SQL.... Any good websites to go to for SQL?