Calculated field in query

[JohnPapa]

I get the error message which appears in the attachment when I try to create a Calculated field using the DateAdd function.

Any ideas?

 

[Gasman]

Do all those fields have values?
If I enter that function
Access CamelCases the function name?
It also surrounds the field names with [ & ]

 

[June7]

Syntax looks right.
I cannot replicate issue.
Null in Number argument triggers a "data type mismatch" error, null in Date argument returns Null.

Will DateAdd work in the VBA Immediate Window?

 

[JohnPapa]

Do all those fields have values?
If I enter that function
Access CamelCases the function name?
It also surrounds the field names with [ & ]

Yes the fields have values.
What is CamelCase?
Not clear what you mean with [&]

 

[June7]

Camel case is capitalizing first letter of each word - so you get "humps" in middle of text string.
Gasman means left and right bracket characters - "[" and "]"

 

[Gasman]

Compare what was entered to what was generated as shown in the code segment.

SELECT tblDaily.DailyDate, tblDaily.Factor, DateAdd("m",[factor],[dailydate]) AS Expr1
FROM tblDaily;

 

[JohnPapa]

When I run the query without the Calculated field I get what appears in the attachment.

 

[June7]

That does not show the dtmCreation field.

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[JohnPapa]

That does not show the dtmCreation field.

If you want to provide db for analysis, follow instructions at bottom of my post.

It shows the dtmCreation field on mine

 

[Gasman]

That does not show the dtmCreation field.

If you want to provide db for analysis, follow instructions at bottom of my post.

You have to click on the image @June7

 

[DHookom]

Based on your location, should you use ";" in place of ","?

DateAdd("m";[factor];[dailydate]) AS Expr1

 

[Gasman]

That was my post Duane.
I was just trying to replicate the issue.

 

[Gasman]

@JohnPapa have you created your own dateadd function by accident?

 

[DHookom]

That was my post Duane.
I was just trying to replicate the issue.

I realize this was your version and expect the OP would understand (and possibly post text SQL versus an image). I wanted something to simply copy and paste.

 

[JohnPapa]

Based on your location, should you use ";" in place of ","?

DateAdd("m";[factor];[dailydate]) AS Expr1

It appears that the following works.

Expr1: DateAdd("m";[lngMHMonth];[tblMedicalHistoryListNew].[dtmCreation])

Where in the documentation does it say to use ";" instead of "," ?

 

[Gasman]

It might not. It all depends on your system seperator.

Very surprised you have not discovered this before.
Your dates appear in UK format, where are you based? In the UK here we use the comma. In Europe, some countries use the semi-colon, but you cannot chop and change AFAIK.

 

[GregDataReno]

Trivial response (dinner on the stove)... you might need to surround your date value with "#" or cast it to date with CDATE() ?

 

[JohnPapa]

It might not. It all depends on your system seperator.

Very surprised you have not discovered this before.
Your dates appear in UK format, where are you based? In the UK here we use the comma. In Europe, some countries use the semi-colon, but you cannot chop and change AFAIK.

Yes, I am very surprised as well.
The culprit is the List Separator, which was set to ";". When I set it to ".", the original expression worked.

 

[DHookom]

JohnPapa’s profile suggests Cyprus. Sadly, I had to google map it

 

[JohnPapa]

JohnPapa’s profile suggests Cyprus. Sadly, I had to google map it

Yes it is a small island in the mediterranean, a stark contrast to Silicon Valley California where I used to live.