Hello fellow programmers, I am having issues with working out some VBA logic. I am working on a large project and I have come to a road block. I will describe 2 tables and 4 fields I am concerned with.
The first table is called "JobsAndQuotes". 2 fields from this table I am concerned of are called "JobNumber"(Short Text) and "Open Job"(Yes/No).
A second table is called "Job Sheet Hours Worked". 2 fields from this table I am concerned of are called "Job Number"(Short Text) and "Hours Worked"(Number).
Here's the part I am focused on:
I need to add up every number in the "Job Sheet Hours Worked" table under the "Hours Worked" field in which that object's "Job Number" is the same as the "JobNumber" in the table "JobsAndQuotes" which also has 'Yes/True' for its "Open Job" field.
All the code I have tried does not work. It adds it up to 0. And every time I tried using arrays, the RecordSets were always equal to "nothing".
--------------------------
Dim dbs As DAO.Database, rst As DAO.Recordset
Set rst = dbs.OpenRecordset("SELECT JobsAndQuotes.JobNumber FROM JobsAndQuotes WHERE (((JobsAndQuotes.[Open Job])=True))")
In this code, "rst" always equals "nothing". I have spent 20 hours now over 4 days trying to get "rst" to equal literally anything by using all kind of test criteria. I have given up on this part as well.
---------------------------
Dim test1 As Double
test1 = DSum("[Hours Worked]", "[Job Sheet Hours Worked]", "([Job Sheet Hours Worked]![Job Number] = [JobsAndQuotes]![JobNumber]")
In this code, I am not even attempting yet to check if the "JobNumber" object also has "true" for its "Open Job" field. Yet, test1 equals 0 even though there is data in the tables which should add up to a number above 0.
Any thoughts would be appreciated. If there is any information I left out, please let me know and I will clarify.