• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1496
  • Last Modified:

Using IIF IsNull In VB

I have a query that I need to produce all of the total values regardless of the Sum.  
Here is my query, i used the IIf(IsNull) but for some reason when I send it to excel it is
just giving me the records with hours in it, whereas I need to see the zero's too.  Can you please give
me any suggestions.  Thanks in advance

James

SqlStat2 = "SELECT Project, Area, Component, IIf(IsNull(Sum(Hours)), 0, Sum(Hours)) AS TotalHours FROM TimeFile " _
    & "WHERE (((Date) <= #" & enddate & "#)) " _
    & "AND Project = '" & cboProject.Text & "'" _
    & " AND Area = 'JACKET' " _
    & "GROUP BY Project, Area, Component ORDER BY Component;"
0
jholokai02
Asked:
jholokai02
  • 25
  • 15
  • 7
  • +2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Instead of IIf(IsNull(Sum(Hours)), use Nz(Sum(Hours),0) as SomeName

-Jim
0
 
codeconquerorCommented:
Take off the IIF portion.  If you run the query as:

SqlStat2 = "SELECT Project, Area, Component, Sum(Hours) AS TotalHours FROM TimeFile " _
    & "WHERE (((Date) <= #" & enddate & "#)) " _
    & "AND Project = '" & cboProject.Text & "'" _
    & " AND Area = 'JACKET' " _
    & "GROUP BY Project, Area, Component ORDER BY Component;"

It should calculate the Null values as 0s.
0
 
jholokai02Author Commented:
I can't use the Nz function in VB.  I get an error.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Okay.  In that case you can 'fake' it by adding this function...

Public Function Nz(vValue as Variant, Optional vValueIfNull as Variant) as Variant

If IsNull(vValue) then
   Nz = vValueIfNull
else
   Nz = vValue
end if

end function
0
 
jholokai02Author Commented:
What is getting me is that the sum(hours) of the component.  some of these don't have any time charged to them as of the end date.  if they don't then it only gives me the hours of the components that do have charged time.  that query dosen't work, i have tried and tried and tried again.  i hope that there is a way to do this.
0
 
jholokai02Author Commented:
do i need to add anything to the query?
0
 
codeconquerorCommented:
If you run the query as:

<QUERY>
SqlStat2 = "SELECT Project, Area, Component, Sum(Hours) AS TotalHours FROM TimeFile " _
    & "WHERE (((Date) <= #" & enddate & "#)) " _
    & "AND Project = '" & cboProject.Text & "'" _
    & " AND Area = 'JACKET' " _
    & "GROUP BY Project, Area, Component ORDER BY Component;"
</QUERY>

It should calculate the Null values as 0s.  And any that have no hours at all will be returned as NULL.  In VB just add the following when you go to work with that field:

<Code>
If Not IsNull(rs.Fields("TotalHours")) Then
      '**** Do whatever you want as if the value was 0
Else
      '**** There is a value so work with it
End If
</CODE>

Or am I misunderstanding what you're trying to do?
0
 
jholokai02Author Commented:
Let me try to break it down.  I have a table that has 4 Fields.  They are Date, Project, Area, Component and Hours.  I am trying to generate a report that looks like this:

Project Name(chosen from a combo box)
Date (Input by user)
Area (Jacket (inside of query)
                                         
                                           Hours to Date
Component1                                  58
Component2                                   0
Component3                                   24
Component4                                   48
Component5                                   0
etc.........


When I try to run my query, I am not getting the zero's (nulls).  All that I am getting is the calculated numbers.  Can I somehow turn them into zero's.  I'm lost.
Thanks for the suggestions.

James
0
 
codeconquerorCommented:
How are you executing the query and what are you quering (SQL)?
0
 
PreeceCommented:
If you are using SQL Server, then this may help:

SqlStat2 = "SELECT Project, Area, Component,

sum(case when IsNull(Hours) then 0 else Hours end) AS TotalHours

FROM TimeFile " _
    & "WHERE (((Date) <= #" & enddate & "#)) " _
    & "AND Project = '" & cboProject.Text & "'" _
    & " AND Area = 'JACKET' " _
    & "GROUP BY Project, Area, Component ORDER BY Component;"
0
 
Julian HansenCommented:
Is it not possible to specify a default value of 0 for the hours field so that it is never null after running

update TimeFile set hours = 0 where IsNull(hours)

or something similar?
0
 
jholokai02Author Commented:
Okay codeconqueror, I have it in the code, but i'm not getting the results that I want.  I run the query as you stated, and when I want to send it, this is what I have:

  rs.Open SqlStat2, dbCon
            While Not rs.EOF
            col = col + 1
                If Not IsNull(rs.Fields("TotalHours")) Then
                    objExcel.Application.Cells(col, 3).Value = rs!TotalHours
                    rs.MoveNext
                Else
            End If
            Wend
Does this look right, or am I doing something wrong.  Thanks

James
0
 
codeconquerorCommented:
This should work the way you want it to:

rs.Open SqlStat2, dbCon, adopenstatic, adlockpessimistic
if not rs.eof then rs.movelast
if not rs.bof then rs.movefirst
if rs.recordcount > 0 then
     do While Not rs.EOF
         row = row + 1
         if isnumeric(trim$(rs.fields("TotalHours") & vbnullstring)) then
              objExcel.Application.Cells(row, 3).Value = rs!TotalHours
         else
              objExcel.Application(row,3).Value = 0
         end if
         rs.movenext
     loop
end if
rs.close
set rs = nothing
0
 
Julian HansenCommented:
jholokai02 - did you see my post regarding using default values?

I suspect this will be a lot easier to implement and will give you the results you want.
0
 
jholokai02Author Commented:
julianH, Where would I put this line of code?  Do i do it in the query or in the output lines of the code.  Please view my previous posts, do you see where i could put it.
0
 
codeconquerorCommented:
Adding the command before opening your recordset should also give you the desired result although depending on how often you're executing the query and how many records you have, it may slow your program down.

>>  From julianH:
>> dbconn.execute("update TimeFile set hours = 0 where IsNull(hours)")
0
 
jholokai02Author Commented:
codeconqueror, the code you gave me is not working.  i'm not sure if i have something wrong or what.  i get the same results that I have been getting.
0
 
codeconquerorCommented:
Try this:

<CODE SNIPPET>
SqlStat2 = "SELECT Project, Area, Component, Sum(Hours) AS TotalHours FROM TimeFile " _
    & "WHERE (((Date) <= #" & enddate & "#)) " _
    & "AND Project = '" & cboProject.Text & "'" _
    & " AND Area = 'JACKET' " _
    & "GROUP BY Project, Area, Component ORDER BY Component;"
dbcon.execute("Update TimeFile Set Hours = '0' Where Hours Is Null")
set rs = new adodb.recordset
rs.cursorlocation = aduseclient
rs.Open SqlStat2, dbCon, adopenstatic, adlockpessimistic
if not rs.eof then rs.movelast
if not rs.bof then rs.movefirst
if rs.recordcount > 0 then
     do While Not rs.EOF
         row = row + 1
         if isnumeric(trim$(rs.fields("TotalHours") & vbnullstring)) then
              objExcel.Application.Cells(row, 3).Value = rs!TotalHours
         else
              objExcel.Application(row,3).Value = 0
         end if
         rs.movenext
     loop
end if
rs.close
set rs = nothing
</CODE SNIPPET>
0
 
jholokai02Author Commented:
SqlStat1 = "SELECT Project.Project_Name, Project.Project_Area, Project.Project_Component, Sum(TimeFile.Hours) AS WeeklyHours " _
        & "FROM Project LEFT JOIN TimeFile ON (Project.Project_Component = TimeFile.Component) AND " _
        & "(Project.Project_Area = TimeFile.Area) AND (Project.Project_Name = TimeFile.Project) " _
        & "WHERE (((TimeFile.Date) Between # " & startdate & "# AND #" & enddate & "#)) " _
        & "GROUP BY Project.Project_Name, Project.Project_Area, Project.Project_Component;"
   
Set Query1 = CurrentDb.QueryDefs("WeeklyHours")
    Query1.SQL = SqlStat1
   
    SqlStat2 = "SELECT Project.Project_Name, Project.Project_Area, Project.Project_Component, Project.Allowable_Time, Sum(TimeFile.Hours) AS TotalHours, IIf(IsNull([WeeklyHours].[WeeklyHours]),0,[WeeklyHours].[WeeklyHours]) AS WeeklyHours" _
        & " FROM (Project LEFT JOIN WeeklyHours ON (Project.Project_Component = WeeklyHours.Project_Component) AND (Project.Project_Area = WeeklyHours.Project_Area) AND (Project.Project_Name = WeeklyHours.Project_Name)) LEFT JOIN TimeFile ON (Project.Project_Component = TimeFile.Component) AND (Project.Project_Area = TimeFile.Area) AND (Project.Project_Name = TimeFile.Project)" _
        & " GROUP BY Project.Project_Name, Project.Project_Area, Project.Project_Component, Project.Allowable_Time, IIf(IsNull([WeeklyHours].[WeeklyHours]),0,[WeeklyHours].[WeeklyHours])" _
        & " HAVING (((Project.Project_Name)='EPL GA227 A') AND ((Project.Project_Area)='JACKET'));"

I did a little something different.  The only thing that I dont like about this is that I have to have the access db open to run this.  Is ther any way that i can take (Set Query1 = CurrentDb.QueryDefs("WeeklyHours")) and somehow make it read from the db regardless if it is open or not?

Also, When I try to get the weekly hours, it gives me a total regardless of the date. I think that I somehow need to reset the second query to give me the hours of the end date.  
 
Thanks for all of the help

James
0
 
jholokai02Author Commented:
Sorry, the error isn't in the weekly hours, its in the total hours.  I need them to stop before or equal to the end date.
0
 
codeconquerorCommented:
I don't suppose that changing the query to say the following makes any difference, huh?

<QUERY>
SqlStat1 = "SELECT Project.Project_Name, Project.Project_Area, Project.Project_Component, Sum(TimeFile.Hours) AS WeeklyHours " _
        & "FROM Project LEFT JOIN TimeFile ON (Project.Project_Component = TimeFile.Component) AND " _
        & "(Project.Project_Area = TimeFile.Area) AND (Project.Project_Name = TimeFile.Project) " _
        & "WHERE TimeFile.Date >= '" & startdate & "' AND TimeFile.Date <= '" & enddate & "'" _
        & "GROUP BY Project.Project_Name, Project.Project_Area, Project.Project_Component;"
</QUERY>
0
 
jholokai02Author Commented:
that query works good.  I just have the problem with the total hours.  sorry for the confussion.  that is in the second query, SqlStat2.
0
 
Julian HansenCommented:
Where is your data stored (Access, SQL Server?)

As part of your table design you would specify a default value. Let me know what database you are using and we can go from there.

0
 
jholokai02Author Commented:
I am using Access
0
 
Julian HansenCommented:
Before doing this it is recommended you make a backup of your data

In Access

If you go to the design of your table (TimeTable)

Select the hours field

In the properties window type in 0 for the default value

Save the table

Open a new query in SQL view and run the following

updatet TimeTable set Hours = 0 where Hours is null

When that is finished run the following query

SELECT Project, Area, Component, Sum(Hours) AS TotalHours FROM TimeFile
WHERE (((Date) <= #SOME_VALID_DATE#))      <----- Change this line to include a valid date value
AND Project = 'SOME_VALID_PROJECT_VALUE'   <----- Change this line to include a valid project value
AND Area = 'JACKET'
GROUP BY Project, Area, Component
ORDER BY Component

Does this return you what you want?





0
 
jholokai02Author Commented:
do i need to include" updatet TimeTable set Hours = 0 where Hours is null
in my code.  if so where, and if it is not req'd, i'm not getting what i want.  still the
same results.
0
 
Julian HansenCommented:
You should not have to include it in your code. After creating the default value and changing all the existing records to that value all new records should get the default so there should not be a need to run the query.

If you are not getting the results then something else is wrong.

Can you do the following test

Create a new table called tblTest with the following fields

recid autonumber (Primary Key)
teststr Text (50)
testint  number (Default = 0 )

Save the table and open as datasheet

Enter the following records

teststr               testint
Test1                  10
Test2                   0   <-- this should be filled in automatically
Test1                  20
Test3                  30

now run the query

select teststr, sum(testint) as Total from tblTest group by teststr

Does this give these results ?
TestSTR  Total
Test1          30
Test2           0
Test 3        30

If so can you tell me what differences (apart from the extra fields and field names) are between the above example and what you are trying to do?



0
 
jholokai02Author Commented:
I have a table that users input data to (from a vb form).  It contains Index_No (autonumber), Date, Employee, Project, Area, Component, and Hours.  This is input from a user form when they put in employee hours.  I have Hours set to 0 by default.
The test query is working like a champ and that is exactly what I want to do.  I just can't figure out why this isn't working out right.  I really appreciate the help.  Any more suggestions?

Regards,
James
0
 
jholokai02Author Commented:
I think that I have figured out why it isn't working.  Some of the Components may not be in the TimeFile table.  Therefore there aren't any hours in there.  Is there any way that I can use a relationship with 2 tables.  I have a Project table which holds Project_Name, Project_Area, and Project_Component.  As of now, I have no relationships between the two tables.

james
0
 
Julian HansenCommented:
How do you want to relate these two tables?

I have read through the posts but things are a little confusing - can you describe exactly what you are trying to do. Describe both tables what's stored in them and what data you want to extract from each of them individually and combined - that might make it a bit easier to solve the problem.

I am going to be away for a couple hours - I will take a look then and post back if the problem has not been solved.

0
 
jholokai02Author Commented:
Okay, here we go.  I have two tables.  

We have a Project that basically has 7 areas(Jacket in this case).  Under each area there are a list of Components.  The Components are where the time is charged.  The Allowable_Time is the time that is given for each component.  The report needs to output the component, Allowable_Time, Total Hours to Date, and Total Hours This Week.  Below are the Tables.    

Project: which includes Project_Name, Project_Area, Project_Component, and Allowable_Time.
TimeFile: which includes Index_No, Date, Project, Area, Component, and Hours.

I need to generate a report in excel that lists these fields.

-Item (Component)
-Item Total Hours (Allowable Time) this is the time that we allow for each component
-Total Hours to Date (All hours up to the ending date)
-Total Hours This Week (All hours between two specific dates)

Thank you


0
 
Julian HansenCommented:
Ok, just a few more questions

1. What is the difference / relationship (if any) between Project.Allowable_Time and TimeFile.Hours ?
2. What is the Project field in the TimeFile for - does this relate in any way to a field in the Project table?
3. What other relationships (if any) are there between the two tables
4. Can you perhaps describe the process by which records are put into the two tables?

With the answers to the above I should have enough info to be able to replicate the situation here.

Julian


0
 
jholokai02Author Commented:
The difference between Project.Allowable_Time and TimeFile.Hours is:
Project.Allowable_Time is the number of hours allowed for the certain component whereas TimeFile.Hours is the number of hours in which anyone puts time in for the component.  Another words, the sum of timefile.hours should not exceed project.Allowable_Time.

The Project field in the TimeFile table is just a project name.  It could conceivably be related to the TimeFile.Hours, but I haven't created any relationships.

I have no relationships

A user will go into the program, choose an employee, choose a project, choose a component, choose an area, and type in the number for the hours.  All of this goes into the TimeFile Table.

I really appreciate the help.  If you need anymore info, please let me know.

Regards,
James
0
 
Julian HansenCommented:
Hi James,

Ok, I have a sneaky suspicion I know what is wrong - I think it might have something to do with the date filter. Are you sure you are not filtering out the 0 values because they don't fall in the date range you are interested in?

Does the following query have "missing" records?

select project, component, sum(hours) as TotalHours from TimeFile
where area = 'JACKET'
group by project, component

This should produce a record for each Project / Component combinations where area = 'JACKET'  with a total hours displayed for each of these combinations.

0
 
jholokai02Author Commented:
Okay,
   

SqlStat1 = "SELECT Project.Project_Name, Project.Project_Area, Project.Project_Component, Sum(TimeFile.Hours) AS WeeklyHours " _
        & "FROM Project LEFT JOIN TimeFile ON (Project.Project_Component = TimeFile.Component) AND " _
        & "(Project.Project_Area = TimeFile.Area) AND (Project.Project_Name = TimeFile.Project) " _
        & "WHERE (((TimeFile.Date) Between # " & startdate & "# AND #" & enddate & "#)) " _
        & "GROUP BY Project.Project_Name, Project.Project_Area, Project.Project_Component;"
   
Set Query1 = CurrentDb.QueryDefs("WeeklyHours")
    Query1.SQL = SqlStat1
   
SqlStat2 = "SELECT Project.Project_Name, Project.Project_Area, Project.Project_Component, Project.Allowable_Time, Sum(TimeFile.Hours) AS TotalHours, IIf(IsNull([WeeklyHours].[WeeklyHours]),0,[WeeklyHours].[WeeklyHours]) AS WeeklyHours" _
& " FROM (Project LEFT JOIN WeeklyHours ON (Project.Project_Component = WeeklyHours.Project_Component) AND (Project.Project_Area = WeeklyHours.Project_Area) AND (Project.Project_Name = WeeklyHours.Project_Name)) LEFT JOIN TimeFile ON (Project.Project_Component = TimeFile.Component) AND (Project.Project_Area = TimeFile.Area) AND (Project.Project_Name = TimeFile.Project)" _
& " GROUP BY Project.Project_Name, Project.Project_Area, Project.Project_Component, Project.Allowable_Time, IIf(IsNull([WeeklyHours].[WeeklyHours]),0,[WeeklyHours].[WeeklyHours])" _
& " HAVING (((Project.Project_Name)='EPL GA227 A') AND ((Project.Project_Area)='JACKET'));"
0
 
jholokai02Author Commented:
Oops, Disregard the previos message

I have this working, except for the hours up to the date entered by the user.  I get the correct hours between the dates.  Now is there any way that I can take Set Query1 = CurrentDb.QueryDefs("WeeklyHours") out of the code or set it to where the database dosen't have to be open?  

 SqlStat1 = "SELECT Project.Project_Name, Project.Project_Area, Project.Project_Component, Sum(TimeFile.Hours) AS WeeklyHours " _
        & "FROM Project LEFT JOIN TimeFile ON (Project.Project_Component = TimeFile.Component) AND " _
        & "(Project.Project_Area = TimeFile.Area) AND (Project.Project_Name = TimeFile.Project) " _
        & "WHERE (((TimeFile.Date) Between # " & startdate & "# AND #" & enddate & "#)) " _
        & "GROUP BY Project.Project_Name, Project.Project_Area, Project.Project_Component;"
   
Set Query1 = CurrentDb.QueryDefs("WeeklyHours")
    Query1.SQL = SqlStat1
   
SqlStat2 = "SELECT Project.Project_Name, Project.Project_Area, Project.Project_Component, Project.Allowable_Time, Sum(TimeFile.Hours) AS TotalHours, IIf(IsNull([WeeklyHours].[WeeklyHours]),0,[WeeklyHours].[WeeklyHours]) AS WeeklyHours" _
& " FROM (Project LEFT JOIN WeeklyHours ON (Project.Project_Component = WeeklyHours.Project_Component) AND (Project.Project_Area = WeeklyHours.Project_Area) AND (Project.Project_Name = WeeklyHours.Project_Name)) LEFT JOIN TimeFile ON (Project.Project_Component = TimeFile.Component) AND (Project.Project_Area = TimeFile.Area) AND (Project.Project_Name = TimeFile.Project)" _
& " GROUP BY Project.Project_Name, Project.Project_Area, Project.Project_Component, Project.Allowable_Time, IIf(IsNull([WeeklyHours].[WeeklyHours]),0,[WeeklyHours].[WeeklyHours])" _
& " HAVING (((Project.Project_Name)='EPL GA227 A') AND ((Project.Project_Area)='JACKET'));"
0
 
Julian HansenCommented:
>> Now is there any way that I can take Set Query1 = CurrentDb.QueryDefs("WeeklyHours") out of the code or set it to where the database dosen't have to be open?  

I am not quite sure what you are asking - could you elaborate?

From above I see you are creating a query dynamically so you can use it in SqlStat2 - but I am not sure what you are asking when you ask if the "Set statement can be taken out of the code or set to where the database doesn't have to be open" ?
0
 
jholokai02Author Commented:
When you run the program, you have to have the database open.  Is there any way that you can set that in code, so that you dont have to open the database everytime that you want to run the query.

Also, did you come up with a better way of doing this.  This seems a little redundant.

Thanks,

James
0
 
Julian HansenCommented:
>> so that you dont have to open the database everytime that you want to run the query.

This is what is confusing me - if you want to get data out the database you have to open it - not sure how this will help if you don't open the db?

>> Also, did you come up with a better way of doing this

Let me understand what you want to do - you want to get the total of all the hours for a component between Date1 and Date2. You then want to combine this information with the Total hours for the component giving you an output potentially of


Project Name  Area        Component Allowable_Time Total Hours   Weekly Hours
Proj1              JACKET       Comp1            10                12                  4
Proj1              JACKET       Comp2            20                  5                  0
Proj2              JACKET       Comp3            25                  0                  0
...

Is that correct?
0
 
jholokai02Author Commented:
yes that is correct.  but please note that the Total hours should be <= ending date.  
0
 
jholokai02Author Commented:
sorry,
the total hours should be a sum of hours on or before the ending date.  another words, there may be 852 hours in the table.  but if someone uses 5/13/2005 for a date then the hours would be less than that.  

5/13/2005 = 858
5/11/2005 = 800
0
 
Julian HansenCommented:
And weekly hours should be the sum of hours between StartDate an EndDate (inclusive or exclusive) ?
0
 
jholokai02Author Commented:
i will get with you tomorrow.  gotta go.  thanks for the help
weekly hours is between the two hours, correct. as for inclusive or exclusive, i'm not sure what you are asking?

thanks again for all of the help
James
0
 
Julian HansenCommented:
Inclusive means

StartDate = 11 May 2005
EndDate = 15 May 2005

Then inclusive means 11,12,13,14,15 May
and exclusive means 12,13,14 May

I am assuming inclusive.

Will post back here later today.

Julian
0
 
jholokai02Author Commented:
you are correct.  the sum of hours is inclusive.  Thanks
0
 
Julian HansenCommented:
I think the solution to your problem is parameterized queries.

I did the following test.

Create the table tblTest with 3 fields

Project Text(50) (Primary Key)
Hours number
datelogged datetime

Fill this information in with sample data Example

Project   Hours DateLogged
Test1       10      1/1/2005
Test2        0       4/4/2005
Test1      20       2/2/2005
Test3      30       3/3/2005

Next create the following queries

qry1:
SELECT Project, sum(Hours) as WeeklyHours
FROM tblTest
WHERE DateLogged BETWEEN startdate AND enddate
GROUP BY PROJECT

qry2:
SELECT Project, sum(Hours) as TotalHours
FROM tblTest
WHERE DateLogged <= enddate
GROUP BY Project

These queries are saved in Access and shouldn't need to change.

Now create a 3rd query (this simulates the call from your VB app - do it in Access first and later implement in code)

SELECT qry1.Project, qry1.WeeklyHours, qry2.TotalHours
FROM qry1 LEFT JOIN qry2 ON qry1.Project = qry2.Project

When you run the 3rd query you will be prompted for the values for startdate and enddate. For this exercise fill these in as
Startdate: 2/1/2005
Enddate:   5/5/2005

Do the results from query 3 correlate to what you want to get from your data?

If not can you explain why not - If they do then we can look at how to call the the 3rd query from code.
0
 
Julian HansenCommented:
The good thing about the above solution is that you don't have to create the queries in code - you just need to supply parameters to them.
0
 
jholokai02Author Commented:
Sorry,
   I was out yesterday afternoon.  I will give this solution a try and let you know how it works.  

James
0
 
jholokai02Author Commented:
Looks good to me.  This looks like the answers that I was looking for.  Now where do I go from here?
Thanks,

James
0
 
Julian HansenCommented:
I am assuming that the above test worked for you and you now need to implement this in code?

Here is a small sample I knocked together that will run against the database I specified above.

1. Create a Form in access
2. Add to text boxes called txtStartDate and txtEndDate
3. Add a command button.
4. Add the following code to the OnClick event for the button

Private Sub Command1_Click()
    Dim cmd As New ADODB.Command
    Dim param As ADODB.Parameter
    Dim rs As ADODB.Recordset
    Dim sQueryName as STring

    sQueryName = "ProjectHours" ' CHANGE This to the name you gave to the third query (as described in previous posts)
    cmd.ActiveConnection = CurrentProject.Connection  'In your VB app change this to your active connection
    cmd.CommandText = sQueryName
    cmd.CommandType = adCmdStoredProc

   ' Create the parameters.
   ' Create a new paramter of type date for input
    Set param = cmd.CreateParameter("@startdate", adDate, adParamInput)
    'Add it to the command parameters collection
    cmd.Parameters.Append param
    ' Convert the txtStartDate value to a Date and assign to the parameter value field
    param.Value = CDate(txtStartDate)
   
    'Repeat above procedure for second parameter
    Set param = cmd.CreateParameter("@enddate", adDate, adParamInput)
    cmd.Parameters.Append param
    param.Value = CDate(txtEndDate)
   
    Set rs = cmd.Execute
   
    If Not rs.EOF Then
        rs.MoveFirst
        While Not rs.EOF
            MsgBox "Project = " & rs("Project") & " Weekly Hours = " & rs("WeeklyHours") & " Total Hours = " & rs("TotalHours")
            rs.MoveNext
        Wend
    End If
   
    rs.Close
End Sub

5. Run the form and enter the dates 2/1/2005 for Startdate and 5/5/2005 for end date. You should get the records displayed one after the other in a message box.

That should do it

Julian
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 25
  • 15
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now