DateAdd Too few parameters. Expected 1

[arnelgp]

of all the time, i have no clue.
thought you are executing it from ms access...

 

[arnelgp]

can you just add 30 (days) * 6 (month) = 180 days ?
or 365 \ 2 days = 182 days?
maybe it will work.


SELECT DateValue(DateOfCal)+182 AS NextCalibration FROM TblCalibItems;

 

[jxwd]

sorry I did mention in my 3rd post... I wasn't sure if it was significant or not I assumed the drivers would "just" run it on Access ...

 

[jxwd]

Yes success
SELECT DateValue(DateOfCal)+182 AS NextCal FROM TblCalibItems;

Things is I need to use another column (MonthsCal) for the integer number of months and sometimes may include Feb etc... so really need to use a function that can do all this accurately...

 

[arnelgp]

just use Days (the Number of months converted to days) field.

can you try making a Public function and calling it in your Query:

Public Function DteAdd(ByVal dte As Variant, ByVal NoOfMonths As Integer) As Date
dte = DateValue(dte)
DteAdd = DateAdd("m", NoOfMonths, dte)
End Function

now try (if it will work):

SELECT DteAdd(DateOfCal, MonthsCal) AS NextCal FROM TblCalibItems;

 

[jxwd]

Tried to declare the function (as a query)
[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.


It doesn't seem to like dateadd?

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
SELECT DATEADD ("month", -2, NOW()) AS NextCalibration FROM TblCalibItems;

 

[jxwd]

Seems there is an issue with ODBC date format and DATEADD

[QODBC-Desktop] Troubleshooting - How to use Date() And DateAdd() function in QODBC - Powered by Kayako Help Desk Software

support.flexquarters.com

I tried:
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
SELECT DATEADD (day, -2, {d '2022-02-02'}) AS NextCalibration FROM TblCalibItems;

 

[jxwd]

And with "

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.
SELECT DATEADD ("day", -2, "{d '2022-02-02'}") AS NextCalibration FROM TblCalibItems;

 

[cheekybuddha]

It looks like you are connecting to a MySQL backend. Is that so?

[Microsoft][ODBC Microsoft Access Driver] looks strange.

What version of MyODBC driver are you using?

Is it 32bit/64bit? Is it ANSI or unicode?

What version server are you using?

 

[jxwd]

Connecting to MS Access, with ODBC driver.

System

Windows NT PICWEB01 6.3 build 9600 (Windows Server 2012 R2 Standard Edition) i586

odbc​

ODBC Support	enabled
Active Persistent Links	0
Active Links	0
ODBC library	Win32
ODBCVER	0x0350

Directive	Local Value	Master Value
odbc.allow_persistent	On	On
odbc.check_persistent	On	On
odbc.default_cursortype	Static cursor	Static cursor
odbc.default_db	no value	no value
odbc.default_pw	no value	no value
odbc.default_user	no value	no value
odbc.defaultbinmode	return as is	return as is
odbc.defaultlrl	return up to 4096 bytes	return up to 4096 bytes
odbc.max_links	Unlimited	Unlimited
odbc.max_persistent	Unlimited	Unlimited

 

[cheekybuddha]

What are you using as frontend? Access?

Why are you connecting Access -> Access using ODBC?

 

[jxwd]

The front end is PHP. Because the IT company installed odbc drivers to interface with ACCESS... is there another way?

 

[cheekybuddha]

I don't know whether DateAdd() is available in Access SQL - it is a VBA function that might not be accessible when accessing via ODBC.

Just grab the date as it appears in the record and process in php.

 

[cheekybuddha]

Do you have direct access to the Access backend?

If so, perhaps create a saved query that uses the DateAdd() function and just select from the query.

 

[jxwd]

It is from what I can read see here: https://support.flexquarters.com/es...how-to-use-date-and-dateadd-function-in-qodbc

The issue seems to be the ODBC date format.

Unfortunately it needs to be sorted by the new value so not very practical to sort in the PHP... but it may come to that...

 

[jxwd]

yes I can get access to the backend if I need... but I have never used it.

 

[jxwd]

NB this is a temporary solution... The next phase is to remove Access... and replace with mySQL

 

[cheekybuddha]

qodbc != odbc

I think it's a specific driver for some accounting package

 

[cheekybuddha]

The next phase is to remove Access... and replace with mySQL

I suggest just skip to this stage now!

 

[Minty]

It is! - https://www.access-programmers.co.uk/forums/threads/quickbooks-pick-your-brains-please.317522/