UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Sum Between Two Dates, Any Version    
 
   
mrdrjay
post Jul 30 2020, 05:33 PM
Post#1



Posts: 95
Joined: 27-June 14



Hello Everyone,

The following code
CODE
SELECT Sum(IIf([HalfHandReQDte] Between [forms]![Administration Details]![PayoutBeginDte] And [forms]![Administration Details]![PayoutEndDte],1,0)) AS [Count of Half Hand], Sum(IIf([FullHandReQDate] Between [forms]![Administration Details]![PayoutBeginDte] And [forms]![Administration Details]![PayoutEndDte],1,0)) AS [Count of Full Hand]
FROM PersDtail;
was the only code that I found that works perfect now when I tried to convert it to VBA I get an Compile error: Wrong number of arguments or invalid property assignment
CODE
= Sum(IIf([HalfHandReQDte], "Between #" & [Forms]![Administration Details]![PayoutBeginDte] & "# And #" & [Forms]![Administration Details]![PayoutEndDte], 1, 0))& "#"
what am I doing wrong? Thanks
Go to the top of the page
 
DanielPineault
post Jul 30 2020, 06:20 PM
Post#2


UtterAccess VIP
Posts: 7,434
Joined: 30-June 11



QUOTE
now when I tried to convert it to VBA I get an Compile error

Can you post your VBA code.

I'm confused by the SQL Statement and then the = expression. How do they relate?

Perhaps something like
CODE
sSQL = "SELECT Sum(IIf([HalfHandReQDte] Between #" & Forms![Administration Details]![PayoutBeginDte] & "# And #" & Forms![Administration Details]![PayoutEndDte] & "#,1,0)) AS [Count of Half Hand]" & _
           ", Sum(IIf([FullHandReQDate] Between #" & Forms![Administration Details]![PayoutBeginDte] & "# And #" & Forms![Administration Details]![PayoutEndDte] & "#,1,0)) AS [Count of Full Hand]" & vbCrLf & _
           " FROM PersDtail;"

--------------------
Daniel Pineault (2010-2020 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
mrdrjay
post Jul 30 2020, 07:50 PM
Post#3



Posts: 95
Joined: 27-June 14



I tried to use condense the SQL to the sumif functions and having more issues with it as of now lets put that one aside.

What I am seeking to do is to create a way to count the number of personnel that requested payments between certain dates for example John, Jay requested 2020-08-04 and Joe, Flow requested on 2020-08-10, etc as it will return the count of personnel that wanted either a full, half or both. The SQL added up the personnel for example 3 full hands and 4 half hands even if counted the same person twice if they have both.

Currently they are 7 testing records that reflect as below
Half: 4
Full: 3
Total: 7

The following code

CODE
Dim HalfHand As Date

HalfHand = DSum("[HalfHandReQDte]", "PersDtail", "[HalfHandReQDte] = #" & [Forms]![Administration Details]![PayoutBeginDte] & "# AND [HalfHandReQDte] =#" & [Forms]![Administration Details]![PayoutEndDte] & "#")
MsgBox HalfHand
produces a run-time error '94' invalid use of Null. I checked the form fields and they are not blank.

What I trying to do is to have the above code cycle through and let me know the specifics. How can I solve this issue? Thanks
Go to the top of the page
 
Gustav
post Jul 31 2020, 04:40 AM
Post#4


UtterAccess VIP
Posts: 2,305
Joined: 21-February 07
From: Copenhagen


You are probably after something like this:

CODE
HalfHand = DCount("*", "PersDtail", "[HalfHandReQDte] = #" & Format([Forms]![Administration Details]![PayoutBeginDte], "yyyy\/mm\/dd") & "# AND #" & Format([Forms]![Administration Details]![PayoutEndDte], "yyyy\/mm\/dd") & "#")

' and:

FullHand = DCount("*", "PersDtail", "[FullHandReQDte] = #" & Format([Forms]![Administration Details]![PayoutBeginDte], "yyyy\/mm\/dd") & "# AND #" & Format([Forms]![Administration Details]![PayoutEndDte], "yyyy\/mm\/dd") & "#")


--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 
mrdrjay
post Jul 31 2020, 07:54 PM
Post#5



Posts: 95
Joined: 27-June 14



Thanks for the response. Unfortunately when I tried, it gave me 0 Halfhand and 1 Fullhand.
Go to the top of the page
 
RJD
post Jul 31 2020, 08:25 PM
Post#6


UtterAccess VIP
Posts: 10,687
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, but it looks to me like we are at the point of needing to see your db to address this. Can you post a db (no sensitive data, relevant objects, zipped)? But with data that would lead us to the counts you indicated in the date span you mentioned.

I anticipate that with the db a solution should be forthcoming from someone here.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
mrdrjay
post Jul 31 2020, 09:24 PM
Post#7



Posts: 95
Joined: 27-June 14



Thanks for the response. As requested please see attached draft db. Thanks
This post has been edited by mrdrjay: Jul 31 2020, 09:26 PM
Attached File(s)
Attached File  Tracker.zip ( 699.55K )Number of downloads: 3
 
Go to the top of the page
 
RJD
post Jul 31 2020, 11:43 PM
Post#8


UtterAccess VIP
Posts: 10,687
Joined: 25-October 10
From: Gulf South USA


Hi: It's late for me, and I am starting to nod off ... but take a look at my revision to your db, and see if this gives you the results you want, from the command buttons on the form and from the queries as well.

I did notice some things that could be issues for you in this db. You have followed varying naming standards for dates, with ...Dte on HalfHand and ...Date on FullHand. This can be the cause of coding errors (see Gustav's code above). You are using # in alias names. Not a good idea. # is used as a date delimiter in Access and in some cases can cause you difficulties in a field name. In one place you used & to add. Use +. And when the numbers to add are actually text (at least one case I found), the + is not enough. You must convert the text to numeric as well ... e.g. Clng(Half) + Clng(Full). You are also using several Table Lookup Fields in tables. Not a good idea. See HERE for some information on that. Yes, MS makes it available so you might be led to believe it is a good idea. But they are difficult to work with, and most (if not almost all) experienced developers tend to avoid them. I would say "all" but I imagine someone would disagree with that. Anyway, see the referenced item.

See if this clears things up for you. Or let us know if you have more questions.

HTH
Joe
Attached File(s)
Attached File  Tracker_Rev1.zip ( 78.94K )Number of downloads: 3
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
mrdrjay
post Aug 1 2020, 12:10 AM
Post#9



Posts: 95
Joined: 27-June 14



Hi, Thanks for the information, time and assistance with this I really appreciate it. It does clear things up for me and I will work on cleaning up it. I have one question instead of Table Lookup fields what options are available? Once again. Thanks
Go to the top of the page
 
Bob007
post Aug 1 2020, 01:21 AM
Post#10



Posts: 11
Joined: 16-May 11



Lookups are best used on forms rather than tables. Tables are for storing data not for users to look at. Forms are for interacting with data. See: http://access.mvps.org/access/lookupfields.htm
Go to the top of the page
 
RJD
post Aug 1 2020, 11:21 AM
Post#11


UtterAccess VIP
Posts: 10,687
Joined: 25-October 10
From: Gulf South USA


Hi again: You are very welcome.

Yes, as Bob007 says, looking up values to put in a field is best done on a form.

Make the table field a textbox rather than a combobox lookup field. Then, on a form, use a combobox to enter/maintain the field value for a record. The combobox gets its value from a new table that contains the possible values for the field. This makes the list much easier to maintain as well. For lists that can be maintained by the user, I usually have a command button on my main form that leads to a list maintenance form, on which the user can select the list to maintain (a form backed by the list table).

This seems a bit complicated, but is easy to set up and maintain without having to delve into the table design to update a value list, as you have designed into some fields in tables. And this is a normal way to deal with this issue.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
tina t
post Aug 1 2020, 11:22 AM
Post#12



Posts: 6,744
Joined: 11-November 10
From: SoCal, USA


QUOTE
I have one question instead of Table Lookup fields what options are available?

a Lookup field in a table is basically a "regular" field with a built-in combobox control. combobox controls are great in forms, bad news in tables. you don't need to actually change the field in the table, just remove the "built-in combobox control". to do that:

1. open the table in Design view, and click on a field that has a Lookup.
2. in the lower part of the window, click on the Lookup tab.

Attached File  Capture1.PNG ( 15.07K )Number of downloads: 0


3. change the DisplayControl property setting from ComboBox to TextBox.

Attached File  Capture2.PNG ( 10.29K )Number of downloads: 0


4. save the table, and close it.

now, when you open the table in Datasheet view and click in that field, you won't see an arrow at the right side, or a droplist. instead you'll see the foreign key value that's actually being stored in the field (usually a number value, from the related table), rather than the user-friendly value (usually text). that's okay, though, because 1) a user should never see a table, all interactions should be via forms, and 2) you can use the combobox control in forms, where they work fine, are very useful, and do not cause any problems.

hth
tina

ah, Joe, beat me to it! ;)
This post has been edited by tina t: Aug 1 2020, 11:24 AM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
RJD
post Aug 1 2020, 01:29 PM
Post#13


UtterAccess VIP
Posts: 10,687
Joined: 25-October 10
From: Gulf South USA


@tina: ah, Joe, beat me to it! wink.gif

Well, not by much! smirk.gif But the same idea. Great minds, and all that ... Hope you are doing well.

Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
tina t
post Aug 1 2020, 03:40 PM
Post#14



Posts: 6,744
Joined: 11-November 10
From: SoCal, USA


@Joe, same back to you, hon, keep safe! :) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
mrdrjay
post Aug 1 2020, 07:49 PM
Post#15



Posts: 95
Joined: 27-June 14



Thank you everyone for your help! From what I understand when creating a table instead of having comboboxes have them as textboxes and in the Form you can change them to comboboxes to utilize lookups, etc.
Go to the top of the page
 
tina t
post Aug 1 2020, 08:54 PM
Post#16



Posts: 6,744
Joined: 11-November 10
From: SoCal, USA


you're welcome, we're all glad to help. good luck with your project. :) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
mrdrjay
post Aug 2 2020, 11:18 PM
Post#17



Posts: 95
Joined: 27-June 14



Yes you all did and Thanks.
Go to the top of the page
 
RJD
post Aug 3 2020, 06:21 AM
Post#18


UtterAccess VIP
Posts: 10,687
Joined: 25-October 10
From: Gulf South USA


thumbup.gif

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th August 2020 - 07:41 AM