UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> VBA Ignoring Right() Function, Access 2016    
 
   
dg_moore
post May 8 2018, 01:28 PM
Post#1



Posts: 261
Joined: 22-April 11



I'm using the Right() function in a sub to build a string for a SQL statement in VBA. If the last 6 characters in the string are WHERE[space] I want the program to branch. But each time the condition is met (If Right(strSQL, 6) = "WHERE " then...) VBA ignores it and continues to run to the next line of code. Any ideas what might be wrong?
Go to the top of the page
 
theDBguy
post May 8 2018, 01:35 PM
Post#2


Access Wiki and Forums Moderator
Posts: 73,235
Joined: 19-June 07
From: SunnySandyEggo


You might check for doubled up spaces.

Just thinking out loud...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
dg_moore
post May 8 2018, 01:38 PM
Post#3



Posts: 261
Joined: 22-April 11



Nope, that's not it. No extra spaces.

Thanks
Go to the top of the page
 
theDBguy
post May 8 2018, 01:45 PM
Post#4


Access Wiki and Forums Moderator
Posts: 73,235
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Can you post the complete code? Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
RJD
post May 8 2018, 01:45 PM
Post#5


UtterAccess VIP
Posts: 8,791
Joined: 25-October 10
From: Gulf South USA


Hi: Just guessing, of course, since we do not have your db and code to look at, but is it possible that strSQL is truncating the space and that the last 5 characters are "WHERE"? You might try examining what is in the last few characters and see what is there when being compared in the if statement...

Just a thought...

Failing that, perhaps you could post a db (relevant objects, no sensitive data, zipped) and someone could take a look. This usually leads to a quick solution.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
zaxbat
post May 8 2018, 02:51 PM
Post#6



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


so, basically, you are saying..... if my SQL string is incomplete.....then....do something....that just sounds improbable. Do you actually make incomplete SQL strings?

I do have some lengthy routines that create an SQL string in stages....so maybe you are doing something like that....and at the end just in case it fell all the way through your logic without getting completed....then you are trying to fix it? Hmmmmm.......I think you might be able to just work on the logic flow and this proposed step would not be necessary.
This post has been edited by zaxbat: May 8 2018, 02:53 PM

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
dg_moore
post May 8 2018, 02:54 PM
Post#7



Posts: 261
Joined: 22-April 11



Here's enough of the code to see what's what:

Dim LN As String, FN As String, NName As String, PrevName As String, MemID As Long
Dim Stat As String, PdThru As Date, State As String, Zpcode As Variant
Dim HasEmail As String, NewSince As Date, Inact As Date, FmPln As Boolean, Notif As Boolean
Dim strSQL As String
Dim Qt As String
Qt = Chr(34)

MemID = Me.txt_MemID
LN = Me.txt_LN
FN = Me.txt_FN
NName = Me.txt_Nickname
PrevName = Me.txt_PrevName
Stat = Me.cbo_Status
PdThru = Me.txt_PaidThrough
State = Me.cbo_State
Zpcode = Me.txt_Zipcode
HasEmail = Me.chk_HasEmail
NewSince = Me.txt_MembDate
Inact = Me.txt_InactiveAsOf
FmPln = Me.chk_FamilyPlan
Notif = Me.chk_NotificationsOnly

strSQL = "SELECT * From qry_GeneralSearch WHERE "

If Right(strSQL, 6) <> "WHERE " Then
If Len(LN) <> 0 Then
strSQL = strSQL & " [LastName] = " & Qt & LN & Qt
End If
End If

If Right(strSQL, 6) <> "WHERE " Then
If Len(FN) <> 0 Then
strSQL = strSQL & " And [FirstName] = " & Qt & FN & Qt
End If
End If


...
Go to the top of the page
 
theDBguy
post May 8 2018, 03:05 PM
Post#8


Access Wiki and Forums Moderator
Posts: 73,235
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Thanks for posting the code. In it, you have two lines with "If Right(strSQL, 6)" in it. Are you having issues with both of them or just one?

PS. Reading the code further gives me the impression of a reversed logic.

1. You assign a string value to strSQL with "WHERE " at the end,
2. Then, you check if strSQL does not end with "WHERE " by using the <> comparison operator.
3. This should result in the If/Then block bypassing the strSQL = strSQL &... assignment line (on both checks).

Is that what you want to do?

Conversely, if you don't assign strSQL with "WHERE " at the end, then the If check will return True which will then execute the strSQL = strSQL &... assignment, but then it would result in an invalid SQL statement.

For example, if you initially assign something like:

strSQL = "SELECT * FROM qry_GeneralSearch"

Then the result of strSQL = strSQL & " [LastName] =" & QT & LN & QT might look something like this:

SELECT * FROM qry_GeneralSearch [LastName] = "SomeValue"

It's now missing the WHERE clause...

Just my 2 cents...
This post has been edited by theDBguy: May 8 2018, 03:15 PM

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
zaxbat
post May 8 2018, 03:16 PM
Post#9



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


This is the way I do it...in this way even if the app conditions give you an empty where clause...the query will still run.

CODE
Dim LN As String, FN As String, NName As String, PrevName As String, MemID As Long
Dim Stat As String, PdThru As Date, State As String, Zpcode As Variant
Dim HasEmail As String, NewSince As Date, Inact As Date, FmPln As Boolean, Notif As Boolean
Dim strSQL As String
Dim Qt As String
DIM whereblurb as string

   Qt = Chr(34)
   MemID = Me.txt_MemID
   LN = Me.txt_LN
   FN = Me.txt_FN
   NName = Me.txt_Nickname
   PrevName = Me.txt_PrevName
   Stat = Me.cbo_Status
   PdThru = Me.txt_PaidThrough
   State = Me.cbo_State
   Zpcode = Me.txt_Zipcode
   HasEmail = Me.chk_HasEmail
   NewSince = Me.txt_MembDate
   Inact = Me.txt_InactiveAsOf
   FmPln = Me.chk_FamilyPlan
   Notif = Me.chk_NotificationsOnly

   strSQL = "SELECT * From qry_GeneralSearch "
   whereblurb = ""

   If Len(LN) <> 0 Then
      if whereblurb = "" then
         whereblurb = " WHERE [LastName] = " & Qt & LN & Qt
      else
         whereblurb = whereblurb & " AND [LastName] = " & Qt & LN & Qt
      End If
   End if

   If Len(FN) <> 0 Then
      if whereblurb = "" then
         whereblurb = " WHERE [FirstName] = " & Qt & FN & Qt
      else
         whereblurb = whereblurb & " AND [FirstName] = " & Qt & FN & Qt
      End If
   End if

   strSQL = strSQL & whereblurb & ";"

This post has been edited by zaxbat: May 8 2018, 03:21 PM

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
dg_moore
post May 8 2018, 04:29 PM
Post#10



Posts: 261
Joined: 22-April 11



I'm building a search form. The form has a bunch of unbound controls for the user to enter search terms and the code assembles a SQL statement to filter the records from a recordset. None of the fields are obligatory and the user can choose the ones he needs. Usually more than one field will be chosen, so they get concatenated in the SQL statement. However, if only one search term is specified I don't want to end up with a statement that says SELECT * From [recordset WHERE And....

I suppose I cold keep a count of how many fields are chosen and use that to steer the SQL. If I can't get the Right () working I'll take a look at that.
Go to the top of the page
 
theDBguy
post May 8 2018, 04:38 PM
Post#11


Access Wiki and Forums Moderator
Posts: 73,235
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Have you looked at the various search form demos in the Code Archive? They should help you build a search form with multiple optional criteria.

Just curious...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
dg_moore
post May 8 2018, 06:34 PM
Post#12



Posts: 261
Joined: 22-April 11



No, I haven't. But I will now.

OK - examplesearch.zip contains a promising approach. Basically you count the number of WHERE's in the SQL statement. If it's = 0, then strSQL = strSQL & " WHERE". If the count is >0, then strSQL = strSQL & " AND ". I think this will do the trick.

Thanks for the suggestion!
This post has been edited by dg_moore: May 8 2018, 06:52 PM
Go to the top of the page
 
zaxbat
post May 8 2018, 07:28 PM
Post#13



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Feeling invisible again. Zaxbat.visible = False

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
dg_moore
post May 8 2018, 08:55 PM
Post#14



Posts: 261
Joined: 22-April 11



Zaxbat.visible=True. Just took a closer look at your post (I must have missed it in all the excitement earlier) and see that your approach does the same thing in principle as the other example, but much more simply. I like it.

Thanks!
Go to the top of the page
 
zaxbat
post May 8 2018, 09:14 PM
Post#15



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


pompom.gif

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th September 2018 - 06:07 PM