Hi, Im attempting to run a query over a remote Db400 table using MS Access'97 where i need to specify the from and to date. Depending on the current date, the required dates will change so I'm using VB to calculate the day number, to then calculate the from and to dates. The function is called by a query, but it fails on a type mismatch as the target field must be defined as a integer. Given this happens, is there an alternative solution, appreciate as much detail as possible as im not very familiar with writing a sql statement. - Public Function CalcDelDate() As String
-
-
Dim FromDelDate As Variant
-
Dim ToDelDate As Variant
-
Dim Today As Variant
-
Dim MyDate As String
-
Dim Interval As String
-
Dim FromDate As String
-
Dim ToDate As String
-
-
'This procedure calculates the date for the following days deliveries
-
'Calculate what the Day Number is from todays date
-
'Where today is a Friday, add 3 days to get to Mondays date
-
'Where today is a Saturday, add 2 days to get to Mondays date
-
-
Today = WeekDay(Date)
-
If Today = 6 Then
-
FromDelDate = DateAdd("d", "1", Date)
-
ToDelDate = DateAdd("d", "3", Date)
-
ElseIf Today = 7 Then
-
FromDelDate = DateAdd("d", "2", Date)
-
ToDelDate = DateAdd("d", "2", Date)
-
Else
-
FromDelDate = DateAdd("d", "1", Date)
-
ToDelDate = DateAdd("d", "1", Date)
-
End If
-
-
'Now Format the calculated date prefixing with a '1' to match as/400 date format
-
-
FromDate = Format(FromDelDate, "1yymmdd")
-
ToDate = Format(ToDelDate, "1yymmdd")
-
CalcDelDate = ">=" & FromDate & " And " & "<=" & ToDate
The WHERE clause, by necessity, is processed before the SELECT clause. IE. It is not pre-worked out. Not in Jet SQL (The SQL engine used by Access) anyway.
Looking again it's not even there in the SELECT clause so I guess you're less experienced even than that. You mean in the VBA code. I'm afraid variables in code modules are not visible to the Jet SQL engine that interprets the string you pass as a SQL command (in line #8). You need to use Date Literals. Literal DateTimes and Their Delimiters (#) should help you with that.
4 2344
All, I've now added the following code in an attempt to write an SQL query while using the calculated dates. When the following code runs, it prompts me to input the FromDate and ToDate, despite it already being calculated earlier in the function. Any advice appreciated - StrSQL = "SELECT AULT2F2_OEP40.CONO40, AULT2F2_OEP40.CUSN40, AULT2F2_OEP40.DSEQ40, AULT1F2_T1P1A.EMIL1A, AULT2F2_OEP40.ORDN40, AULT2F2_OEP40.DTDR40" _
-
& " INTO [HOSTLocal]" _
-
& " FROM (AULT2F2_OEP40 INNER JOIN AULT2F2_OEP40E ON (AULT2F2_OEP40.CONO40 = AULT2F2_OEP40E.CONO40) AND (AULT2F2_OEP40.ORDN40 = AULT2F2_OEP40E.ORDN40)) INNER JOIN AULT1F2_T1P1A ON (AULT2F2_OEP40.CONO40 = AULT1F2_T1P1A.CONO1A) AND (AULT2F2_OEP40.CUSN40 = AULT1F2_T1P1A.TPAC1A) AND (AULT2F2_OEP40.DSEQ40 = AULT1F2_T1P1A.DSEQ1A)" _
-
& " WHERE (((AULT2F2_OEP40.CONO40)='SG') AND ((AULT2F2_OEP40.ORDN40) Not Like 'R%') AND ((AULT2F2_OEP40.DTDR40) BETWEEN FromDate AND ToDate) AND ((AULT2F2_OEP40.CUSO40) Not Like 'AUTODROP%') AND ((AULT2F2_OEP40.OSBT40)<>'E' And (AULT2F2_OEP40.OSBT40)<>'A') AND ((AULT2F2_OEP40E.PBTP40)<>'1'))"
-
-
DoCmd.SetWarnings False
-
-
DoCmd.RunSQL StrSQL
NeoPa 32,556
Recognized Expert Moderator MVP
The WHERE clause, by necessity, is processed before the SELECT clause. IE. It is not pre-worked out. Not in Jet SQL (The SQL engine used by Access) anyway.
Looking again it's not even there in the SELECT clause so I guess you're less experienced even than that. You mean in the VBA code. I'm afraid variables in code modules are not visible to the Jet SQL engine that interprets the string you pass as a SQL command (in line #8). You need to use Date Literals. Literal DateTimes and Their Delimiters (#) should help you with that.
Thanks ive sorted, left the WHERE clause in the same location but added " & FromDate & " AND " & ToDate & "
NeoPa 32,556
Recognized Expert Moderator MVP
That's the first (major) step towards getting it right and understanding, but if you read through the linked article you'll see that's not the full story. They are date literals so need to be formatted to the specific SQL format (otherwise you will have a USA specific version only (and other locales that use m/d/yyyy format of course) that works correctly).
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Stewart Allen |
last post by:
I'm trying to filter a table that has 2 date fields, the first date will
always have a value but the second will only occasionally has a value. Each
date field also has a corresponding text field...
|
by: Megan |
last post by:
Hi everybody-
I'm trying to use a checkbox to control whether or not a date field in
a query "Is Null" or "Is Not Null."
I have 2 date fields: InDate and OutDate. If there is an OutDate, then...
|
by: RoadRunner |
last post by:
Hi, I have a employee vacation database that has a vacation table that
has the employee name, pay week and date of vacation. I have another
lookup table with pay week code and date range for the...
|
by: DhavalPatel1983 |
last post by:
Hi,
I need help in VB Datareport, my problem describe as below....
I have one Bank application project with all the information like Deposite,Clearness,Check,Cash,Creadit,ATM...
|
by: flumpuk |
last post by:
Hi
My job currently requires me to enter data from 300+ forms a month.
The system which we used in Excel was slow , and theprevious guy had
three workbooks for this job .
I have created...
|
by: assgar |
last post by:
Hi
I need help.
I know what I want to accomplish, but I do not know how to do it.
WHAT I NEED HELP ACCOMPLISHING:
How to do I insert data into a table for a date range of...
|
by: RoadRunner |
last post by:
Hi, I am having a problem. I have a very simple employee database.
The client needs to see everything on a form before any updates or
deletions can be made. I have a form that loads with two...
|
by: zandiT |
last post by:
hello again
i have almost finished with my database. i have decided to generate the reports by using a date or date range and i can't get it to work. first i used parameters in a query but its...
|
by: phill86 |
last post by:
Hi
I am re-posting this thread because it has become very confusing and I have got some way to solving the problem so it is a slightly different question from the initial thread.
here is the...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |