Sumif with And Vlookup in Access VBA

[mahmed1]

Hi Guys

what is the equivalent of doing a sumif or theses formula in Access VBA

TotalHoursColumn =Sumifs([TotalHours],[Date],@[Date])
TotalRecColumn =@[TotalHours]/@[Received]
FteCol = @[AHT]/Vlookup(@[Area],TableAreaLookup,3,0)
Im trying to avoid doing it in excel and build it in to the access table using Vba

 

[alansidman]

Build a query and use the group by built in functionality. Lose the spreadsheet thought process and adopt the database thought process. They are not the same.

 

[mahmed1]

Hi
This is new to me and was hoping someone could help with a VBA approach

 

[alansidman]

SQL Tutorial

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[JonXL]

Hi
This is new to me and was hoping someone could help with a VBA approach

To conditionally sum a field in VBA, the most straightforward approach is to use DSum().

DSum Function - Microsoft Support

support.office.com

For writing the SQL-esque criteria, reference alansidman's link. If there is still anything that you can't work out, post back and we'll go from there.

 

[mahmed1]

Thankyou

i have tried this

TotalReceived = DSum("[ContactsReceived]","[tblContacts]","[MyId]='" & [id_] & "'" And "[Date_]<=#" & [Date_] & "#")

I get no results using this

 

[Micron]

This "'" before the word And leaves And outside of quotes. That will never work. Also, these make no sense
- [Date]<=#" & [Date_] & "#" You have a field named [Date] (not good)? If so, you are saying "Where date field value is less than or equal to date field value". It can only be equal to itself, never less.
- You have [Date] in your original post and [Date_] in that expression. Only one of them can be correct?
- are you trying to say "Where today's date ( written as Date() ) is less than or equal to the value in a field named [Date] (or [Date_] ) ?

 

[JonXL]

TotalReceived = DSum("[ContactsReceived]","[tblContacts]","[MyId]='" & [id_] & "'" And "[Date_]<=#" & [Date_] & "#")

I get no results using this

A lot depends on where you are putting this.

As Micron pointed out, it's not really clear what your variables ([id_] and [Date_]) are intended to refer to. So it will help to troubleshoot to know where this code is going and where the variables are coming from.

Also, your criteria expression should be written like the WHERE clause of a SQL statement (see this post). Here, using the forum's formatting tools can actually be very helpful; when you wrap your DSum() statement in VBA tags, the problem Micron mentions of the AND being outside (rather than inside) the criteria statement becomes rather clear - it is blue but should be orange.

TotalReceived = DSum("[ContactsReceived]","[tblContacts]","[MyId]='" & [id_] & "'" And "[Date_]<=#" & [Date_] & "#")

vs...

TotalReceived = DSum("[ContactsReceived]","[tblContacts]","[MyId]='" & [id_] & "' And [Date_]<=#" & [Date_] & "#")