DateAdd Too few parameters. Expected 1

[jxwd]

Hi I am trying to run the following query:

SELECT DateAdd("m", 6, DateOfCal) FROM TblCalibItems;

IE adding an integer number of 6 months to a date DateOfCal.


Any pointers? Thanks

 

[arnelgp]

you check if DateOfCal is Null:

SELECT DateAdd("m", 6, Nz([DateOfCal], Date())) As NextCalibration FROM TblCalibItems;

 

[jxwd]

Thanks Ahhh starting to make some sense, not sure how to check if it is null (it didn't recognize Nz) I tried:


SELECT DateAdd("m", 6, IsNull([DateOfCal], Date())) As NextCalibration FROM TblCalibItems;

I got - Wrong number of arguments used with function in query expression 'DateAdd([m], 6, IsNull([DateOfCal], Date()))'.

I tried a few other things not sure what to try next?

 

[arnelgp]

what is your Locale "separator" comma (,) or semicolon (

 

[jxwd]

I have no idea what this means...

 

[arnelgp]

DateAdd("m", 6, IsNull([DateOfCal], Date()))

see the "double" qoute.
the above will work if your Locale is American-English.

Some other countries uses semicolon ;; as their separator:

DateAdd("m"; 6; IsNull([DateOfCal], Date()))

 

[jxwd]

Ahh I see, super useful to know... (I am running this via ODB remotely on a database I did not create, so I am quite disconnected form the things, and I normally use mysql... so not up to speed yet...)


I ran the above query: SELECT DateAdd("m"; 6; IsNull([DateOfCal], Date())) As NextCalibration FROM TblCalibItems;

Syntax error in query expression 'DateAdd([m]; 6; IsNull([DateOfCal], Date()))'.

 

[arnelgp]

use double quote here also you are missing IIF()

DateAdd([m]; 6; IsNull([DateOfCal], Date()))

should be:

DateAdd("m"; 6; Nz([DateOfCal], Date()))

 

[jxwd]

thank you for baring with me!

I tried (I tried single quotes too)-

SELECT DateAdd("m"; 6; Nz([DateOfCal], Date())) As NextCalibration FROM TblCalibItems;

Syntax error in query expression 'DateAdd([m]; 6; Nz([DateOfCal], Date()))'.

 

[arnelgp]

can you post your db.
i am the only one seeing this.
you are using [m], instead of "m"

 

[arnelgp]

you also need to replace the comma (,) to semicolon in Nz() function:

Nz([DateOfCal]; Date())

 

[jxwd]

The query does have "m" ... it's after it's interpreted that it goes to [m]... fixed the second , ... thanks

So the query is: SELECT DateAdd("m"; 6; Nz([DateOfCal]; Date())) As NextCalibration FROM TblCalibItems;

Response: [Microsoft][ODBC Microsoft Access Driver] Syntax error in query expression 'DateAdd([m]; 6; Nz([DateOfCal]; Date()))'.



I am not running MS Access locally. But here is the SQL version of the table definition if that helps? I can get more info if needed...

CREATE TABLE `TblCalibItems` (
`CalibItemID` INTEGER NOT NULL AUTO_INCREMENT,
`EquiptID` INTEGER,
`SerialNo` VARCHAR(40),
`ConsignID` INTEGER NOT NULL,
`Status` INTEGER DEFAULT 0,
`CalMethod` LONGTEXT,
`Procedure` VARCHAR(50),
`Comment` LONGTEXT,
`CommentInt` LONGTEXT,
`DateOfCal` DATETIME,
`MonthsCal` TINYINT(3) UNSIGNED,
`Repair` TINYINT(1),
`JobDone` TINYINT(1),
`Result` INTEGER,
`LocationID` INTEGER,
`EmpID2` INTEGER,
`EmpID` INTEGER,
`TestCondID` INTEGER,
`CalBasisID` INTEGER,
`CertFomat` INTEGER,
`CertificateNo` VARCHAR(50),
`RecaNote` TINYINT(1) DEFAULT 0,
`TestTol` VARCHAR(50),
`Supply` INTEGER NOT NULL DEFAULT 0,
`ProcID` INTEGER,
`OutOfUse` TINYINT(1),
`CertProt` TINYINT(1),
INDEX (`CalBasisID`),
UNIQUE (`CertificateNo`),
INDEX (`ConsignID`),
INDEX (`EmpID`),
INDEX (`EquiptID`),
INDEX (`LocationID`),
PRIMARY KEY (`CalibItemID`),
INDEX (`ProcID`)
) ENGINE=myisam DEFAULT CHARSET=utf8;

 

[arnelgp]

change DateOfCal to Date (not DateTime).
when you use DateTime in access, the value is converted to String.

 

[jxwd]

I used DateValue() to convert. Got a syntax error I removed the ";" and put ","... fixed the syntax error... then

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'Nz' in expression.
SELECT DateAdd("m", 6, Nz(DateValue(DateOfCal), Date())) AS NextCalibration FROM TblCalibItems;



Didn't like Nz so I used

[Microsoft][ODBC Microsoft Access Driver] Wrong number of arguments used with function in query expression 'DateAdd([m], 6, IsNull(DateValue(DateOfCal), Date()))'.
SELECT DateAdd("m", 6, IsNull(DateValue(DateOfCal), Date())) AS NextCalibration FROM TblCalibItems;


Then I tried

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
SELECT DateAdd("m", 6, DateValue(DateOfCal)) AS NextCalibration FROM TblCalibItems;

 

[arnelgp]

try:

SELECT DateAdd("m", 6, DateValue(Nz([DateOfCal], Date()))) AS NextCalibration FROM TblCalibItems;

 

[jxwd]

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'Nz' in expression.
SELECT DateAdd("m", 6, DateValue(Nz([DateOfCal], Date()))) AS NextCalibration FROM TblCalibItems;


And tried:

[Microsoft][ODBC Microsoft Access Driver] Wrong number of arguments used with function in query expression 'DateAdd([m], 6, DateValue(IsNull([DateOfCal], Date())))'.
SELECT DateAdd("m", 6, DateValue(IsNull([DateOfCal], Date()))) AS NextCalibration FROM TblCalibItems;


With ; syntax error so won't use those from now on.

 

[arnelgp]

sorry again, DateValue will not work since DateTime is a String.
you need to create a function first to Convert the String to Real Date:

Public Function MSSQLDateToAccessDate(ByVal sDate As Variant) As Variant
    Dim matches, match
    Dim v() As Integer
    If IsNull(sDate) Then Exit Function
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = True
        .Pattern = "([0-9]{4})\-([0-9]{1,2})\-([0-9]{1,2})\ ([0-9]{2})\:([0-9]{2})\:([0-9]{2})"
        Set matches = .Execute(sDate)
        If Not IsNull(matches) Then
            
            MSSQLDateToAccessDate = _
                DateSerial(Val(matches(0).submatches(0)), Val(matches(0).submatches(1)), Val(matches(0).submatches(2))) + _
                TimeValue(matches(0).submatches(3) & ":" & matches(0).submatches(4) & ":" & matches(0).submatches(5))
        End If
    End With
End Function

SELECT SELECT DateAdd("m", 6, DateValue(MSSQLDateToAccessDate(Nz(DateOfCal, Format(Now(),"yyyy-mm-dd hh:nn:ss) & ".000"))) AS NextCalibration FROM TblCalibItems;

 

[Minty]

PMFJI but a DateTime won't be interpreted as a String in Access, A DateTime2 might be, depending on the ODBC Driver being used and the version of Access.

Are you running this in a pass-through query?

If you open the table in design view what datatype does it think it is?

 

[jxwd]

Not sure what a pass-though query is... I am sending the query to this method in PHP if that helps?
///////////////////////////////////////////////////////////////////////////////////
// db_query - this does global error handling... all subsiquent chould use this...
///////////////////////////////////////////////////////////////////////////////////
//
function db_query ($query, $error_message) {

$result = odbc_exec($this->ms_access, $query);
if (odbc_error($this->ms_access)) die(odbc_errormsg($this->ms_access)."<br />".$query."<br />".$error_message);
else return $result;

}




Thanks both I just ran:

SELECT DateValue(DateOfCal) AS NextCalibration FROM TblCalibItems;

without any issues... so guess the type is correct?

 

[jxwd]

Then if I use with DateAdd I get

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
SELECT DateAdd("m", 6, DateValue(DateOfCal)) AS NextCalibration FROM TblCalibItems;