OPERAND help

[lashwarj]

I am recieving an error saying missing operand. It is in the portion of my code where I do a select * from database

 

[danceman]

An operator that requires two operands was used without the second one.

check each oprand '(' and look to see that you have matching ')'

If you are building a string to do a SQL, maybe its in the build of the string. Attitude is Everything

 

[lashwarj]

Below is the Code I have in a command button that recieves the error. I have places a @@@@@@@@ at the bottom near the error




LOCAL lcphotonum,lcdonor,lcphotographer,lodate_aquired,lcdate_photo,lcsize,lcdesc,lcother,lclocation
lcphotonum = UPPER(ALLTRIM(ThisForm.Photo_Number.Value))
lcdonor = UPPER(ALLTRIM(ThisForm.Donor.Value))
lcphotographer = UPPER(ALLTRIM(ThisForm.Photographer.Value))
lcdate_aquired = UPPER(ALLTRIM(ThisForm.Date_aquired.Value))
lcdate_photo = UPPER(ALLTRIM(ThisForm.Date_Photo.Value))
lcsize = UPPER(ALLTRIM(ThisForm.Size.Value))
lcdesc = UPPER(ALLTRIM(ThisForm.Desc_Search.Value))
lcother = UPPER(ALLTRIM(ThisForm.Other.Value))
lclocation = UPPER(ALLTRIM(ThisForm.Location.Value))


STORE '' TO criteria

IF ThisForm.Photo_num_Check.Value = 1
criteria = " lcphotonum $(upper(historian.photo_num)) "
ENDIF

IF ThisForm.Donor_Check.Value = 1
criteria = criteria + " AND " + CHR(13)
ENDIF
criteria = criteria + ;
" lcdonor $(upper(historian.donor)) "


IF ThisForm.photographer_Check.Value = 1
criteria = criteria + " AND " + CHR(13)
ENDIF
criteria = criteria + ;
" lcphotographer = $(UPPER(historian.photo_take)) "


IF ThisForm.aquired_Check.value = 1
IF LEN(ALLTRIM(criteria)) > 0
criteria = criteria + " AND " + CHR(13)
ENDIF

criteria = criteria + ;
" lcdate_aquired = $(UPPER(historian.date_aquir)) "
ENDIF


SELECT * FROM historian ;
WHERE &criteria ;
INTO CURSOR cfoundhistrecs

SELECT cfoundhistrecs
SET SAFETY OFF
COPY TO temp
SET SAFETY ON
DO FORM search_historian

 

[lashwarj]

Below is the Code I have in a command button that recieves the error. I have places a at the bottom near the error




LOCAL lcphotonum,lcdonor,lcphotographer,lodate_aquired,lcdate_photo,lcsize,lcdesc,lcother,lclocation
lcphotonum = UPPER(ALLTRIM(ThisForm.Photo_Number.Value))
lcdonor = UPPER(ALLTRIM(ThisForm.Donor.Value))
lcphotographer = UPPER(ALLTRIM(ThisForm.Photographer.Value))
lcdate_aquired = UPPER(ALLTRIM(ThisForm.Date_aquired.Value))
lcdate_photo = UPPER(ALLTRIM(ThisForm.Date_Photo.Value))
lcsize = UPPER(ALLTRIM(ThisForm.Size.Value))
lcdesc = UPPER(ALLTRIM(ThisForm.Desc_Search.Value))
lcother = UPPER(ALLTRIM(ThisForm.Other.Value))
lclocation = UPPER(ALLTRIM(ThisForm.Location.Value))


STORE '' TO criteria

IF ThisForm.Photo_num_Check.Value = 1
criteria = " lcphotonum $(upper(historian.photo_num)) "
ENDIF

IF ThisForm.Donor_Check.Value = 1
criteria = criteria + " AND " + CHR(13)
ENDIF
criteria = criteria + ;
" lcdonor $(upper(historian.donor)) "


IF ThisForm.photographer_Check.Value = 1
criteria = criteria + " AND " + CHR(13)
ENDIF
criteria = criteria + ;
" lcphotographer = $(UPPER(historian.photo_take)) "


IF ThisForm.aquired_Check.value = 1
IF LEN(ALLTRIM(criteria)) > 0
criteria = criteria + " AND " + CHR(13)
ENDIF

criteria = criteria + ;
" lcdate_aquired = $(UPPER(historian.date_aquir)) "
ENDIF


SELECT * FROM historian ;
WHERE &criteria ;
INTO CURSOR cfoundhistrecs

SELECT cfoundhistrecs
SET SAFETY OFF
COPY TO temp
SET SAFETY ON
DO FORM search_historian

 

[danceman]

You do not need to put chr(13) and ';'

criteria = criteria + " AND " + CHR(13)

this is done in code when editing manually just for looks
try leaving out the chr(13) and the ';'
Attitude is Everything

 

[lashwarj]

Now I get a unrecognized command verb on the line with the


SELECT * FROM historian
WHERE &criteria
INTO CURSOR cfoundhistrecs
SELECT cfoundhistrecs
SET SAFETY OFF
COPY TO temp
SET SAFETY ON
DO FORM search_historian

 

[Gandalf23]

If your criteria has spaces in in then &criteria wont work.
Leave the & away and use (criteria) instead. With the bracklets.

 

[danceman]

try this technique

put the statement together then wrtie the varible that is holding everything to a file (text file). then you can do a modi file on it to review it for syntax. then copy it to memory, go to command window and try it out. Attitude is Everything

 

[DSummZZZ]

You've left off some ENDIFs which will make the command read as:

lcphotonum $(upper(historian.photo_num)) lcdonor $(upper(historian.donor)) lcdate_aquired = $(UPPER(historian.date_aquir))

with no AND ;

IF ThisForm.Photo_num_Check.Value = 1
criteria = " lcphotonum $(upper(historian.photo_num)) "
ENDIF

IF ThisForm.Donor_Check.Value = 1
criteria = criteria + " AND " + CHR(13)
ENDIF
criteria = criteria + ;
" lcdonor $(upper(historian.donor)) "
ENDIF

IF ThisForm.photographer_Check.Value = 1
criteria = criteria + " AND " + CHR(13)
ENDIF
criteria = criteria + ;
" lcphotographer = $(UPPER(historian.photo_take)) "
ENDIF

IF ThisForm.aquired_Check.value = 1
IF LEN(ALLTRIM(criteria)) > 0
criteria = criteria + " AND " + CHR(13)
ENDIF

criteria = criteria + ;
" lcdate_aquired = $(UPPER(historian.date_aquir)) "
ENDIF

The reason I stated using "AND ;" + CHR(13) is because if the statement gets too long, you will get an error. It will also be more readable when debugging.

Dave S.

 

[lashwarj]

The big problem now is not the operand I dont even know if I will get that anymore because now I recieve a unrecognized command verb where the is


SET SAFETY OFF
SELECT * FROM historian
WHERE &criteria
INTO CURSOR cfoundhistrecs ;
COPY TO temp
SET SAFETY ON
DO FORM search_historian

 

[lashwarj]

The big problem now is not the operand I dont even know if I will get that anymore because now I recieve a unrecognized command verb where the is


SET SAFETY OFF
SELECT * FROM historian
WHERE &criteria
INTO CURSOR cfoundhistrecs ;
COPY TO temp
SET SAFETY ON
DO FORM search_historian

 

[DSummZZZ]

Ok. I debugged the statement as best I could without having a form with your checkboxes on it. Note that there are a lot of differences between your prior statements and this one. I didn't even notice the other IF tests you were leaving out from yesterdays banter:

#DEFINE CRLF CHR(13) + CHR(10)

IF ThisForm.Photo_num_Check.Value = 1
criteria = " lcphotonum $(upper(historian.photo_num)) "
ENDIF

IF ThisForm.Donor_Check.Value = 1
IF !EMPTY(criteria)
criteria = criteria + " AND ;" + CRLF
ENDIF
criteria = criteria + ;
" lcdonor $(upper(historian.donor)) "
ENDIF

IF ThisForm.photographer_Check.Value = 1
IF !EMPTY(criteria)
criteria = criteria + " AND ;" + CRLF
ENDIF
criteria = criteria + ;
" lcphotographer = $(UPPER(historian.photo_take)) "
ENDIF

IF ThisForm.aquired_Check.value = 1
IF !EMPTY(criteria)
criteria = criteria + " AND ;" + CRLF
ENDIF

criteria = criteria + ;
" lcdate_aquired = $(UPPER(historian.date_aquir)) "
ENDIF

You were getting the unrecognized command verb because you left off the ;'s on the query statement, and added one at the end after 'cfoundhistrecs'.

SET SAFETY OFF
SELECT * FROM historian ;
WHERE &criteria ;
INTO CURSOR cfoundhistrecs

COPY TO temp
SET SAFETY ON
DO FORM search_historian

Make sure you copy this reply verbatim!

Dave S.

p.s. Will this ever go away?

 

[lashwarj]

Copied it verbatim and it compiled ok but as soon as you try to do a search on more then one field it gives you the missing operand. I am sorry this is such a pain in the a@@. I have been spending to much time on this and I know you have all been trying hard to assist me in it. thank you.

 

[danceman]

this is a method I use when ever I have a large and mulitable build of macro substitution and I keep getting errors. I write the macro out to a text file, that way I can view the actual code I think I am getting. also I can use copy and paste to the command window to try the code. Attitude is Everything

 

[DSummZZZ]

Woops. I just saw another boo-boo. Soory, I didn't catch it (them) before. The problem with these statements is the '=':

" lcphotographer = $(UPPER(historian.photo_take)) "
" lcdate_aquired = $(UPPER(historian.date_aquir)) "

It needs to be removed, just leave these lines like:
" lcphotographer $(UPPER(historian.photo_take)) "
" lcdate_aquired $(UPPER(historian.date_aquir)) "

The missing operand problem is that the '=' is trying to get a value assigned instead of just using the substring search ($) function.

Dave S.

 

[lashwarj]

Still giving it, also I just realized I need to have a value in the first if or else I can not run this. How would i prevent that.

 

[DSummZZZ]

The criteria variable should be initialized like this:

#DEFINE CRLF CHR(13) + CHR(10)

STORE '' TO criteria

IF ThisForm.Photo_num_Check.Value = 1
criteria = " lcphotonum $(upper(historian.photo_num)) "
ENDIF

I know that was in the code previously, but somehow it got omitted. The IF !EMPTY(criteria) section should deal with criteria being empty after the first IF though. Would you put in a break point and copy/paste the value of criteria? Just before the SELECT statement, SET STEP ON or just put a breakpoint in the debugger. Then in the command window, use this line:

_CLIPTEXT = criteria

Then, paste the result in this forum so I/we can see it.

Dave S.

 

[lashwarj]

STILL DOING THE OPERAND ERROR. HERE IS ALL THE CODE IN THE COMMAND BUTTON:::::::::::




LOCAL lcphotonum,lcdonor,lcphotographer,lodate_aquired,lcdate_photo,lcsize,lcdesc,lcother,lclocation
lcphotonum = UPPER(ALLTRIM(ThisForm.Photo_Number.Value))
lcdonor = UPPER(ALLTRIM(ThisForm.Donor.Value))
lcphotographer = UPPER(ALLTRIM(ThisForm.Photographer.Value))
lcdate_aquired = UPPER(ALLTRIM(ThisForm.Date_aquired.Value))
lcdate_photo = UPPER(ALLTRIM(ThisForm.Date_Photo.Value))
lcsize = UPPER(ALLTRIM(ThisForm.Size.Value))
lcdesc = UPPER(ALLTRIM(ThisForm.Desc_Search.Value))
lcother = UPPER(ALLTRIM(ThisForm.Other.Value))
lclocation = UPPER(ALLTRIM(ThisForm.Location.Value))

#DEFINE CRLF CHR(13) + CHR(10)

STORE '' TO criteria

IF ThisForm.Photo_num_Check.Value = 1
criteria = " lcphotonum $(upper(historian.photo_num)) "
ENDIF

IF ThisForm.Donor_Check.Value = 1
IF !EMPTY(criteria)
criteria = criteria + " AND ;" + CRLF
ENDIF
criteria = criteria + ;
" lcdonor $(upper(historian.donor)) "
ENDIF

IF ThisForm.photographer_Check.Value = 1
IF !EMPTY(criteria)
criteria = criteria + " AND ;" + CRLF
ENDIF
criteria = criteria + ;
" lcphotographer $(UPPER(historian.photo_take)) "
ENDIF

IF ThisForm.aquired_Check.value = 1
IF !EMPTY(criteria)
criteria = criteria + " AND ;" + CRLF
ENDIF

criteria = criteria + ;
" lcdate_aquired $(UPPER(historian.date_aquir)) "
ENDIF

SET SAFETY OFF
SELECT * FROM historian ;
WHERE &criteria ;
INTO CURSOR cfoundhistrecs

COPY TO temp
SET SAFETY ON
DO FORM search_historian

 

[lashwarj]

STILL DOING THE OPERAND ERROR. HERE IS ALL THE CODE IN THE COMMAND BUTTON::::::::::: I mean if you search on any one field it works great but as soon as you try to do it by two it gives the missing operand




LOCAL lcphotonum,lcdonor,lcphotographer,lodate_aquired,lcdate_photo,lcsize,lcdesc,lcother,lclocation
lcphotonum = UPPER(ALLTRIM(ThisForm.Photo_Number.Value))
lcdonor = UPPER(ALLTRIM(ThisForm.Donor.Value))
lcphotographer = UPPER(ALLTRIM(ThisForm.Photographer.Value))
lcdate_aquired = UPPER(ALLTRIM(ThisForm.Date_aquired.Value))
lcdate_photo = UPPER(ALLTRIM(ThisForm.Date_Photo.Value))
lcsize = UPPER(ALLTRIM(ThisForm.Size.Value))
lcdesc = UPPER(ALLTRIM(ThisForm.Desc_Search.Value))
lcother = UPPER(ALLTRIM(ThisForm.Other.Value))
lclocation = UPPER(ALLTRIM(ThisForm.Location.Value))

#DEFINE CRLF CHR(13) + CHR(10)

STORE '' TO criteria

IF ThisForm.Photo_num_Check.Value = 1
criteria = " lcphotonum $(upper(historian.photo_num)) "
ENDIF

IF ThisForm.Donor_Check.Value = 1
IF !EMPTY(criteria)
criteria = criteria + " AND ;" + CRLF
ENDIF
criteria = criteria + ;
" lcdonor $(upper(historian.donor)) "
ENDIF

IF ThisForm.photographer_Check.Value = 1
IF !EMPTY(criteria)
criteria = criteria + " AND ;" + CRLF
ENDIF
criteria = criteria + ;
" lcphotographer $(UPPER(historian.photo_take)) "
ENDIF

IF ThisForm.aquired_Check.value = 1
IF !EMPTY(criteria)
criteria = criteria + " AND ;" + CRLF
ENDIF

criteria = criteria + ;
" lcdate_aquired $(UPPER(historian.date_aquir)) "
ENDIF

SET SAFETY OFF
SELECT * FROM historian ;
WHERE &criteria ;
INTO CURSOR cfoundhistrecs

COPY TO temp
SET SAFETY ON
DO FORM search_historian

 

[rgbean]

Since you expand the macro at runtime, I don't know the reason for the embedded ;'s and CRLF's. I believe these would only be needed if you were writing the code out to a text file and then compiling it.

Change all the (3 in the code above):
criteria = criteria + " AND ;" + CRLF
To:
criteria = criteria + " AND "

Rick