Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 872
  • Last Modified:

Dsum and date problem

Hello experts ,

i use dsum to calculate the sum of the values of a table column ( name : failed_logins ) based on criteria -> Current date formatted as of "dd/mm/yyyy"

So this is my vba code :
sum_f = dsum("FAILED_LOGINS","ER_RL_GE_LOG","LOGIN_DATE=#" & format(date,"dd/mm/yyyy") & "#" )

Open in new window


The value of sum_f is always null .
How can this be solved because there is no logic on this error
0
stathisx
Asked:
stathisx
1 Solution
 
mbizupCommented:
Try formatting both sides of the expression the same way and use string delimiters:

sum_f = dsum("FAILED_LOGINS","ER_RL_GE_LOG","Format(LOGIN_DATE,'dd/mmyyyy')='" & format(date,"dd/mm/yyyy") & "'" ) 

Open in new window

0
 
stathisxAuthor Commented:
It doesn;t work. In fact LOGIN_DATE is another column of my ER_RL_GE_LOG table so it has to be like this (i mean the criteria)  :
LOGIN_DATE=# <date_formatted>#

I cant even use hard coded date to test the result of dsum , so it makes me suspicious that the syntax is wrong .
0
 
als315Commented:
You may have problems with some regional settings and # around date.
Try:
sum_f = dsum("FAILED_LOGINS","ER_RL_GE_LOG","LOGIN_DATE=date()" ) 

Open in new window

if you need only current date and LOGIN_DATE has date/time format
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
stathisxAuthor Commented:
this works but i have one more question.
if i distribute my application and the system date is of this kind of format (mm/dd/yyyy) instead of (dd/mm/yyyy) the function wont work .
is that true or there is a misunderstood from my side ?
0
 
Dale FyeCommented:
What is the datatype of your [Failed_Logins] column?
0
 
stathisxAuthor Commented:
it is number ( long integer )
0
 
als315Commented:
Date values are stored in DB in internal format and have no regional information, so code:
LOGIN_DATE=date()
will work with any regional settings, you will have problems only if date is stored as text.
0
 
stathisxAuthor Commented:
login_date is stored as date/time type. Will there be any problem with that ?
0
 
als315Commented:
No, this is correct, all will be good if you will not convert date to text and back.
0
 
stathisxAuthor Commented:
thank you very much .
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now