insert a If statement in my vba

[mattaus]

Hello all,

I am counting contacts through using the field "SCHDL_REFNO" as my count field and i am listing all the 'contacts' per each financial year month using the field "SchduleDate".

The problem occurs when there ahve been no contacts for a certain month. If there have been no contacts for the month I would like my IIF statment to put a "0" in rather then just skip it.

Please find my sql below-Plaese can sombebody insert the if stament in the correct place within my statment below...

Sub Test_Reporta()
Dim AppExcel As Object
Dim LOCReport As Recordset
Dim LOCReport2 As Recordset


' Stops warnings from appearing
DoCmd.SetWarnings False

' ********************************************************************************************

Set AppExcel = CreateObject("excel.application")
AppExcel.Visible = True
' Opens Excel template

'Selects Specialty
Select Case [Forms]![Test]![lstSpecialty]
Case "DNU"
AppExcel.Workbooks.Open "C:\Documents and Settings\msundhu\Desktop\Copy of DNU Activity_Revised.xls", , True
Case Else
MsgBox "No Valid Specialty selected"
Exit Sub
End Select
'***********************************************************************************************
'Defines variable to queries records
'Extract all Referrals
AppExcel.StatusBar = "Running Referrals"

Select Case [Forms]![Test]![lstSpecialty]
Case "DNU"

AppExcel.StatusBar = "Group Contacts"
strSql = "SELECT dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"") AS [Date], Count(dbo_vwSchedules.SCHDL_REFNO) AS CountOfSCHDL_REFNO INTO tblGroupContacts " & vbCrLf & _
"FROM dbo_vwSchedules INNER JOIN dbo_tblSchedules ON dbo_vwSchedules.PARNT_REFNO = dbo_tblSchedules.SCHDL_REFNO " & vbCrLf & _
"WHERE (((dbo_vwSchedules.ServiceID) Like ""dnu"") AND ((dbo_tblSchedules.SATYP_REFNO) Like ""1452"") AND ((dbo_vwSchedules.SchduleDate) Between [forms]![Test]![txtStartDate] And [forms]![Test]![txtEndDate])) " & vbCrLf & _
"GROUP BY dbo_vwSchedules.Service, Format([SchduleDate],""yyyymm"");"
DoCmd.RunSQL strSql

Set LOCReport = CurrentDb.OpenRecordset("SELECT tblGroupContacts.Service, tblGroupContacts.Date, tblGroupContacts.CountOfSCHDL_REFNO FROM tblGroupContacts")

'selects named excel worksheet
Set datasheet = AppExcel.ActiveWorkBook.Sheets("RawData")
RepType = 1
Call Report_Run13(LOCReport, datasheet, RepType)


Many Thanks.....matthew

 

[rainman89]

you might want to look into the NZ function

 

[mattaus]

hey thanks rainman but i am still noty sure how to incorparate this in my vba....i have llimited brain cells!!!

 

[DCrake]

Are you getting no records for the period or a blank record for the period? if the latter wrap Nz(Count(...),0) around your counting code.

David

 

[mattaus]

I am getting no records at all for certain months....so basicaly null values..

 

[DCrake]

As you are grouping by the month of activity if no activity is present for a certain month then the group will not exist, which is correct. You need to look at using a cross tab query to get the periods on the horizontal and by using the In() command for column headings you will be able to display periods with no activity.

If you could post a sample db to look at it may help.

David

 

[mattaus]

Thanks for your reply..i will look into the In() syntax..i am fairly new to this..


i am using a cross tab and tgere no figuers for certain months-it would be very helpful if i could get it to bring out the month name even if there are no records for that month...

I have attached a screenshot of my query for you to look at...

Thanks again...

 

[mattaus]

Sorry david,

Please now find the attachement correctly attached..the previous file was to large so it has now been compressed..

Many Thanks,

matthew

 

[DCrake]

Couple of things.

Firstly, your query is ok but it can only be run from the designated form, if you wanted to call this from somewhere else the form would have to open for it to work. I tend to advocate the use of public variables and functions to obtain the parameters. See this Link for directions.

Also in your date column if you go to properties and under column headings you hard code the column headings for the period then the missing months will appear.

such as:

200901, 200902, 200903,etc

This can be done in vba if you have the knowledge to do so.

David

 

[mattaus]

ok thanks mate