Trying to make an IF statement work - complete novice!

[Steph]

Okay before the nitty gritty code part I shall try and explain what I am trying to acheive!

I am trying to make a form that will display the 'kWh' used per month per site.

I have a table of data from suppliers that details the amount used, the start date of the bill and the end date of the bill.

What I would like to acheive would be to get the kWh to display in the end month but have dashes in all the months it covers.

EG 246 kWh used in Jan Feb Mar and Apr.
I would like it to look like:
Jan |Feb |Mar |Apr
- | - | - |246

Right now I can get the kWh to display in the end month using End_Date but I cannot make the dashes go in the months before this.

I created another field called 'Period' (which is End_Date -1day) and tried to the code below to make it work:

Private Sub Form_Load()
'Display kWh or a dash if it falls between a
'period when the form loads
If IsNull(kWh) Or kWh = "" Then
If Start_Date < Period Then
kWh = "-"
End If
End If
End Sub

But this isn't doing anything to my data. I think maybe I am doing the complete wrong thing here so if anyone can point me in the right direction I would be grateful.

Hope that all made sense!

 

[rainman89]

Are Start_Date and Period both date fields? And stored as such?

 

[Steph]

Yes they are

 

[DCrake]

What you want to start looking at is a crosstab query using the month of the consumption as a column heading, grouping by customer as row headings summing the kilowatts.

David

 

[Steph]

I tried this, the problem with that was whilst I could get the data to display correctly I couldn't add the dashes in because I can't group more than one thing by value (I spent hours trying to work out a code that worked) so I thought I would try a form instead which apparently has a tad more flexibility.

 

[DCrake]

When you say you can't group by more than one thing by value, I don't follow. Your month parts would be your column headings and you values would be your row contents. Is the fact that you want dashes purely a cosmetic exercise?

David

 

[Steph]

I would have the kWh as the value - but to add the dashes in would require an IIf statement or a second field with the start date in, but in order to get that to display in the correct place it would also need to be sorted by values else it would be another column and would make no sense.

The reason why I wanted the dashes in is that when I am checking through each site I can see if we have an invoice missing or not. At the moment there are a lot of gaps between each month but I know some of those will be covered by an invoice if you understand me? It doesn't have to be dashes it can be a letter or anything, just something to show that data has been recevied for that month.

Currently I am doing this manually in excel but it is very time consuming. I was hoping I could automate it to save time and possible "manual" errors.

 

[joh024]

I would have the kWh as the value - but to add the dashes in would require an IIf statement or a second field with the start date in, but in order to get that to display in the correct place it would also need to be sorted by values else it would be another column and would make no sense.

The reason why I wanted the dashes in is that when I am checking through each site I can see if we have an invoice missing or not. At the moment there are a lot of gaps between each month but I know some of those will be covered by an invoice if you understand me? It doesn't have to be dashes it can be a letter or anything, just something to show that data has been recevied for that month.

Currently I am doing this manually in excel but it is very time consuming. I was hoping I could automate it to save time and possible "manual" errors.

I don't know how you have your stuff layed out, but you could always create a crosstab as a new table then reopen it in a module in order to update the fields to match your goal. Something like this:

Public Function updaterecords()
    Dim rst As DAO.Recordset
    Dim fldCount As Integer
 
 
    Set rst = CurrentDb.OpenRecordset("MyTableName")
 
    rst.MoveFirst
 
    ''Count number of columns
    For Each f In rst.Fields
        fldCount = fldCount + 1
    Next
 
    'loop through each record
    Do While Not rst.EOF
 
        'If record meets whatever criteria then update each column
        If rst.Fields(1) < Forms!myForm!myFormField Then
            For x = 2 To fldCount
                rst.Fields(x) = rst.Fields(x) & " - something"
            Next
        End If
 
        ''Next record
        rst.MoveNext
    Loop
 
End Function

 

[HiTechCoach]

Steph,

Here is a demo that has two charts, kW hours and power factor as the Y-axis, both on the same time X-axis.

See:
Charts as Trend Displays in Access

Hope you find this useful ...

 

[Steph]

joh024 - thanks I will have a play with this and see if I can make it work.

HiTechCoach - this looks very interesting many thanks for posting this I may be able to use it.

Steph