473,320 Members | 2,111 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQL statement in VBA to accomodate no end date in query

Don
Hi,
I would like my users to be able to enter STARTDATE and leave the
ENDDATE open in the query by form that I have built. Using some code
from another group post I have this within my SQL statement:

strMySQL = strMySQL & "And ((Forms.frmMainSearch.STARTDATE Is Null) Or
(Forms.frmMainSearch.ENDDATE Is Null)" & vbCrLf
strMySQL = strMySQL & "Or ([" & InventorySource & "].COLLDATE BETWEEN
Forms.frmMainSearch.STARTDATE AND
IIf(NZ(Forms.frmMainSearch.ENDDATE),#12/31/2999#))" & vbCrLf

However, I keep getting a syntax error. Any ideas on what needs to be
changed? The SQL statement worked wonderfully prior to this portion of
code being entered. The whole statement is too large to post.

Thanks in advance for any input!

Oct 4 '06 #1
3 2029
Don wrote:
strMySQL = strMySQL & "And ((Forms.frmMainSearch.STARTDATE Is Null) Or
(Forms.frmMainSearch.ENDDATE Is Null)" & vbCrLf
strMySQL = strMySQL & "Or ([" & InventorySource & "].COLLDATE BETWEEN
Forms.frmMainSearch.STARTDATE AND
IIf(NZ(Forms.frmMainSearch.ENDDATE),#12/31/2999#))" & vbCrLf
If you are using code to construct your SQL, why bother with query by form?

What I do is this.

I have two text boxes on my form, one txtFrom, the other txtTo. The
idea being users can enter two dates for a range, or leave txtTo empty
and have all results after and on the date in txtFrom, or leave txtFrom
empty and have all results upt to and on the date in txtTo.

Your section of your SQL construction that deals with the date would
simply be (this is air code and I assume there is more stuff in the
where clause before we get to the dates - I also have lots of line
continuations here so we don't wrap too much - chr(35) is the # sign):

strMySql = "SELECT <whatever"
strMySql = strMySql & "FROM <whatever"
strMySql = strMySql & "WHERE <whatever"

with Forms.frmMainSearch

if not isnull(.txtFrom) and not isnull(.txtTo) then
'Both date boxes are filled, so it's a range
strMySql = strMySql & " and [" & _
InventorySource & "].COLLDATE >= " & _
chr(35) & .txtFrom & chr(35)
strMySql = strMySql & " and [" & _
InventorySource & "].COLLDATE <= " & _
chr(35) & .txtTo & chr(35)
Elseif not isnull(.txtFrom) and isnull(.txtTo) then
'Only from date specified
strMySql = strMySql & " and [" & _
InventorySource & "].COLLDATE >= " & _
chr(35) & .txtFrom & chr(35)
elseif isnull(.txtFrom) and not isnull(.txtTo) then
'Only To date is specified
strMySql = strMySql & " and [" & _
InventorySource & "].COLLDATE <= " & _
chr(35) & .txtTo & chr(35)
end if

end with

This also allows you to run your query for all dates, ie, the user does
not put any date range into it.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
Oct 4 '06 #2
Tim's suggestion isn't bad, but I'd simplify it further and remove all
the "If, Then, Else" junk.

'remove the word SPACE and replace it with a space.
if isnull(me.txtTo) or me.txtTo = "SPACE" then
me.txtTo = #12/31/2099#
end if

Then run Tim's statement assuming you have both "from" and "to" dates.
You could do the same above for the From date as well, if it's NULL set
it to #1/1/1900# or whatever you want to start from.
Don wrote:
Hi,
I would like my users to be able to enter STARTDATE and leave the
ENDDATE open in the query by form that I have built. Using some code
from another group post I have this within my SQL statement:

strMySQL = strMySQL & "And ((Forms.frmMainSearch.STARTDATE Is Null) Or
(Forms.frmMainSearch.ENDDATE Is Null)" & vbCrLf
strMySQL = strMySQL & "Or ([" & InventorySource & "].COLLDATE BETWEEN
Forms.frmMainSearch.STARTDATE AND
IIf(NZ(Forms.frmMainSearch.ENDDATE),#12/31/2999#))" & vbCrLf

However, I keep getting a syntax error. Any ideas on what needs to be
changed? The SQL statement worked wonderfully prior to this portion of
code being entered. The whole statement is too large to post.

Thanks in advance for any input!
Oct 4 '06 #3
I'm not very Access savvy & use it only occasionally. But here goes:

In my parameter form I have 2 text boxes with a StartDate & an EndDate.
The default values of the text boxes are set using the DMax & DMin
variables which poll the Table for the Earliest & Last date & fill up
the text boxes accordingly in the FormOpen event.

Now the user can either accept the default values or overtype his
requirements.

Is this a better option ?

I'd appreciate any input on this.

Thx & Best Rgds,
Prakash.

ManningFan wrote:
Tim's suggestion isn't bad, but I'd simplify it further and remove all
the "If, Then, Else" junk.

'remove the word SPACE and replace it with a space.
if isnull(me.txtTo) or me.txtTo = "SPACE" then
me.txtTo = #12/31/2099#
end if

Then run Tim's statement assuming you have both "from" and "to" dates.
You could do the same above for the From date as well, if it's NULL set
it to #1/1/1900# or whatever you want to start from.
Don wrote:
Hi,
I would like my users to be able to enter STARTDATE and leave the
ENDDATE open in the query by form that I have built. Using some code
from another group post I have this within my SQL statement:

strMySQL = strMySQL & "And ((Forms.frmMainSearch.STARTDATE Is Null) Or
(Forms.frmMainSearch.ENDDATE Is Null)" & vbCrLf
strMySQL = strMySQL & "Or ([" & InventorySource & "].COLLDATE BETWEEN
Forms.frmMainSearch.STARTDATE AND
IIf(NZ(Forms.frmMainSearch.ENDDATE),#12/31/2999#))" & vbCrLf

However, I keep getting a syntax error. Any ideas on what needs to be
changed? The SQL statement worked wonderfully prior to this portion of
code being entered. The whole statement is too large to post.

Thanks in advance for any input!
Oct 7 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: mark | last post by:
Access 2000: I creating a report that has a record source built by the user who selects the WHERE values. An example is: SELECT * FROM CHARGELOG WHERE STDATE Between #10/27/2003# And...
4
by: Polly | last post by:
I had a macro that ran a parameter query and created and opened an Excel file with the system date as part of the file name, but I had to change the file name by hand. So I converted the macro to...
5
by: m_t_hill | last post by:
Running MS Access 2000 MS Windows XP Pro This has caused me a lot of hair loss in the last few days so would appreciate any help. I am running code to append/update a local access database...
3
by: Andy_Khosravi | last post by:
I have been trying to build a user friendly search engine for a small database I have created. I'm having some particular problems with one of my date fields. Here's the setup: I'm using...
14
by: Siv | last post by:
Hi, I just discovered that if in an ADO.NET query I use: "Select * from Invoices Where InvoiceDate BETWEEN StartDate AND EndDate;" In this case StartDate would be 1st of month and EndDate...
6
by: Twobridge | last post by:
I hope someone can help me out with my problem. I have found a sql statement that basically pulls all bills filed within a certain time period and the payments made on those bills with in the...
1
by: Matt | last post by:
I am writing a DELETE statement and I want to filter the records using another SELECT statement. My SELECT statement is a GROUP BY query that grabs all social security numbers from the "Data...
2
by: execworks | last post by:
I would appreciate help with the following Function. The SQL statement below returns a Recordcount = 0, yet, when it runs in the query designer it returns the correct count of 40 Private...
3
by: Joshepmichel | last post by:
Please to help me to following problem I want to do this 1. create Table Name MEMBER on the Database Name "mytestdb", 2. Add the Values to the Table through the Key board Inputs during running...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.