By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,526 Members | 2,207 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,526 IT Pros & Developers. It's quick & easy.

SQL statement in VBA to accomodate no end date in query

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.