Help with Like statement

  • Thread starter Thread starter Gary.Crider
  • Start date Start date
G

Gary.Crider

Dim Y As String
X = Forms!frm1!Combo0.Column(1)
Y = "SELECT Bid FROM" & X & "ORDER BY Bid;"
z = "SELECT table1.feild1, table2.feild1, ..., ..., FROM table1,
table2, .., .., WHERE (..,..,(Like '*-" & Y & "-*'))"
me.recordsource = z

When i get here to the end of my SQL statement I want to place Y into
the like clause. It will compile like this, but when I go to get the
report from the form it will not pick up the correct value that sould
be in Y. I don't believe that it is picking up any value there. This
part of the statement seperates 8 different reports and I thought there
would be a way to do this.
Do i have it all wrong? I figure it is something easy, but I just can't
get it right now.
 
Dim Y As String
X = Forms!frm1!Combo0.Column(1)
Y = "SELECT Bid FROM" & X & "ORDER BY Bid;"
z = "SELECT table1.feild1, table2.feild1, ..., ..., FROM table1,
table2, .., .., WHERE (..,..,(Like '*-" & Y & "-*'))"
me.recordsource = z

When i get here to the end of my SQL statement I want to place Y into
the like clause. It will compile like this, but when I go to get the
report from the form it will not pick up the correct value that sould
be in Y. I don't believe that it is picking up any value there. This
part of the statement seperates 8 different reports and I thought there
would be a way to do this.
Click to expand...


Just parking an SQL statement in a string variable doesn't
do anything. Even if you do open a recordset on the SQL
statement, since the query Y will return every record in
table X, I don't have a clue how that coud possibly be used
with Like in a where clause.

Might be a good time to back up and explain what you are
tring to accomplish and the date tables involved.
 
I am not quite sure I understand what you need but one suggestion is that the
line:
Y = "SELECT Bid FROM" & X & "ORDER BY Bid;"
should be:
Y = "SELECT Bid FROM " & X & " ORDER BY Bid;"
that is; insert a space after FROM and before ORDER so that, if x = 22, it
doesnt concatenate like "SELECT Bid FROM22ORDER BY Bid;"
, but like "SELECT Bid FROM 22 ORDER BY Bid;"

Besides that, the SQL statement you attempt to assign to z looks all wrong
to me, I dont know what your goal is, and you should probably explain that to
the newsgroup
 
I am sorry here is what the code really looks like:

'Grabs the branch that is selected on the form from the drop down
X = Forms!frmDetailbyPeriod_test!Combo0.Column(1)
'Grabs the Like "*-00-*" which tells what branch has been choosen
Y = "SELECT BranchID FROM" & X & "ORDER BY BranchID;"

'SQL string selects all of the feilds needed for the report
Z = " SELECT dbo_PORCPAH.RCPHSEQ AS IDINVC, dbo_PORCPAL.DAYENDSEQ AS
CNTBTCH, dbo_PORCPAL.RCPAHSEQ AS CNTITEM, dbo_PORCPAH.FISCYEAR AS
FISCYR, dbo_PORCPAH.FISCPERIOD AS FISCPER, AccPacAPVendor.VENDNAME,
dbo_PORCPAL.GLITEM AS IDGLACCT, dbo_PORCPAL.FCEXTTOTAL AS AMTGLDIST,
AccPacGLMasterFile.ACCTDESC, 1 AS TEXTTRX,
IIf([texttrx]=3,[amtgldist]*-1,[amtgldist]) AS total,
dbo_PORCPAH.TRANSDATE AS DATEINVC"
Z = Z & " FROM ((dbo_PORCPAH INNER JOIN dbo_PORCPAL ON
(dbo_PORCPAH.DAYENDSEQ = dbo_PORCPAL.DAYENDSEQ) AND
(dbo_PORCPAH.RCPAHSEQ = dbo_PORCPAL.RCPAHSEQ)) INNER JOIN
AccPacGLMasterFile ON dbo_PORCPAL.GLITEM =
AccPacGLMasterFile.ACCTFMTTD) INNER JOIN AccPacAPVendor ON
dbo_PORCPAH.VENDOR = AccPacAPVendor.VENDORID"
Z = Z & " WHERE(((dbo_PORCPAH.FISCYEAR) =
[Forms]![mainmenu]![cboIEContractYear]) And ((dbo_PORCPAH.FISCPERIOD) =
[Forms]![mainmenu]![cboIEAccPacMnths]) And ((dbo_PORCPAL.GLITEM) Like
'*-03-*'))"
Z = Z & " ORDER BY dbo_PORCPAL.GLITEM;"

Me.RecordSource = Z

The like statement that contains the "*-03-*" is the only difference
between 8 queries. I was hoping that I would be able to insert what
number I would want there in order to get slim down the amount of
queries in my database. I thought that the Y = statement above would
return the number that I want, but I guess I am not fully understanding
what it returns, because when I try to put it in the middle of the like
statement it does not seems to be reading anything.

So basicly what I am trying to accomplish is to put the proper branchID
in the middle of the like statement to I can pull the proper info. Can
you point me in the right direction
 
I am sorry here is what the code really looks like:

'Grabs the branch that is selected on the form from the drop down
X = Forms!frmDetailbyPeriod_test!Combo0.Column(1)
'Grabs the Like "*-00-*" which tells what branch has been choosen
Y = "SELECT BranchID FROM" & X & "ORDER BY BranchID;"

'SQL string selects all of the feilds needed for the report
Z = " SELECT dbo_PORCPAH.RCPHSEQ AS IDINVC, dbo_PORCPAL.DAYENDSEQ AS
CNTBTCH, dbo_PORCPAL.RCPAHSEQ AS CNTITEM, dbo_PORCPAH.FISCYEAR AS
FISCYR, dbo_PORCPAH.FISCPERIOD AS FISCPER, AccPacAPVendor.VENDNAME,
dbo_PORCPAL.GLITEM AS IDGLACCT, dbo_PORCPAL.FCEXTTOTAL AS AMTGLDIST,
AccPacGLMasterFile.ACCTDESC, 1 AS TEXTTRX,
IIf([texttrx]=3,[amtgldist]*-1,[amtgldist]) AS total,
dbo_PORCPAH.TRANSDATE AS DATEINVC"
Z = Z & " FROM ((dbo_PORCPAH INNER JOIN dbo_PORCPAL ON
(dbo_PORCPAH.DAYENDSEQ = dbo_PORCPAL.DAYENDSEQ) AND
(dbo_PORCPAH.RCPAHSEQ = dbo_PORCPAL.RCPAHSEQ)) INNER JOIN
AccPacGLMasterFile ON dbo_PORCPAL.GLITEM =
AccPacGLMasterFile.ACCTFMTTD) INNER JOIN AccPacAPVendor ON
dbo_PORCPAH.VENDOR = AccPacAPVendor.VENDORID"
Z = Z & " WHERE(((dbo_PORCPAH.FISCYEAR) =
[Forms]![mainmenu]![cboIEContractYear]) And ((dbo_PORCPAH.FISCPERIOD) =
[Forms]![mainmenu]![cboIEAccPacMnths]) And ((dbo_PORCPAL.GLITEM) Like
'*-03-*'))"
Z = Z & " ORDER BY dbo_PORCPAL.GLITEM;"

Me.RecordSource = Z

The like statement that contains the "*-03-*" is the only difference
between 8 queries. I was hoping that I would be able to insert what
number I would want there in order to get slim down the amount of
queries in my database. I thought that the Y = statement above would
return the number that I want, but I guess I am not fully understanding
what it returns, because when I try to put it in the middle of the like
statement it does not seems to be reading anything.

So basicly what I am trying to accomplish is to put the proper branchID
in the middle of the like statement to I can pull the proper info.
Click to expand...


Man, this is confusing. Now the Y query doesn't appear to
have anything to do with anything. The way you have the Y
query, it's looking for all the BranchIDs in table 03???
But, then Y is not used anywhere??

As far as I can tell, all you want is:

. . . And ((dbo_PORCPAL.GLITEM) Like '*-" & X & "-*'))"
 
I am sorry the -03- is a code in a table that tells what brach it is
assigned to. It is a long code that has a ceritan set of two numbers
in the middle that tells me what branch it is looking at. This is why
I would like to see if I could get this number into the Like statement.


In the combo box on the form it drops down so the user would be able to
select what brach they want, they would not know what IDs correspond to
what branch. So I have a table set up that holds that branches and the
IDs that go along with them. I thought that by making the Y it would
just return the one branch ID that goes along with the branch name that
is selected.

I am pretty new to this, but I thought that I would be able to do
something like this. Where in the same table I have the branch name
and branch ID. If I have a user hit the drop down to select the branch
name, how do I get that to correspond to the correct branch ID?

Before I had the statement set up like: .... And ((dbo_PORCPAL.GLITEM)
Like '*-" & Y & "-*'))" but did not pay attention that I was feeding
all of the ID's in, and this why it was not working. I would like to
do something like what it done with reading the X into the Like
statement, but I do not know how to make the X the ID that I want,
right now it is the branch name.

I have a lookup table with the branch name and IDs, this is where the
values for the drop down come from, now I would like to know how to get
the IDs out of this table that correspond with what branch name the
user selected

I don't know if this clears anything up or just more confusing.. Sorry
but I am having a hard time trying to explain
 
Sorry to bother you, I was just doing some really dumb things.. I hae
it all worked out.. Thank you for you time, and again sorry to bother
you
 
Sorry to bother you, I was just doing some really dumb things.. I hae
it all worked out.. Thank you for you time, and again sorry to bother
you
Click to expand...


No problem.

I think your combo box already returns the BranchID and
that's why you were referring to its Column property. That
means that the Y query really is unecessary and why I think
my suggestion to just use ... Like "*-" & X & "-*" ahould
work.
 
Back
Top