If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
query expression using form
I have an unbound text box in one of the forms. In the text box, the user
enters a date. Once the date is entered, the query needs to pick that date from the form. Right now, I did an update query using a dumpy date. Here is the SQL statement: UPDATE Galdump INNER JOIN (STAFF INNER JOIN STAFF_PHONE_NUMBERS ON STAFF. ST_ID = STAFF_PHONE_NUMBERS.ST_ID) ON (Galdump.country = STAFF_PHONE_NUMBERS. GALCountry) AND (Galdump.forename = STAFF_PHONE_NUMBERS.ST_FIRST_NM) AND (Galdump.city = STAFF_PHONE_NUMBERS.Location) SET STAFF.EmailSource = "10/6/2006", Galdump.Indication = "Y-Match First Name & Country & City", STAFF.st_email = Galdump.mailaddress WHERE (((STAFF.EmailSource) Not Like "10/6/2006" And (STAFF.EmailSource) Not Like "not outlook" And (STAFF.EmailSource) Not Like "checked*" And (STAFF. EmailSource) Not Like "NIG*") AND ((Galdump.Indication) Is Null) AND ((Left( [galdump].[surname],5))=Left([staff].[st_last_nm],5))) OR (((STAFF. EmailSource) Is Null) AND ((Galdump.Indication) Is Null) AND ((Left([galdump]. [surname],5))=Left([staff].[st_last_nm],5))); Whereever the date, I need to put an expression: [forms]! [frmExtractInformation]![txtDate]. But this doesn't work. Can you help me find the correct expression that needs to be inserted next "Not Like"? Please note that the field that contains the date is a "text" data type, not a "date/time" date type. If you need more explanation, please let me know. Thank you. |
#2
|
|||
|
|||
query expression using form
test
-- NTC "pjacob" wrote: I have an unbound text box in one of the forms. In the text box, the user enters a date. Once the date is entered, the query needs to pick that date from the form. Right now, I did an update query using a dumpy date. Here is the SQL statement: UPDATE Galdump INNER JOIN (STAFF INNER JOIN STAFF_PHONE_NUMBERS ON STAFF. ST_ID = STAFF_PHONE_NUMBERS.ST_ID) ON (Galdump.country = STAFF_PHONE_NUMBERS. GALCountry) AND (Galdump.forename = STAFF_PHONE_NUMBERS.ST_FIRST_NM) AND (Galdump.city = STAFF_PHONE_NUMBERS.Location) SET STAFF.EmailSource = "10/6/2006", Galdump.Indication = "Y-Match First Name & Country & City", STAFF.st_email = Galdump.mailaddress WHERE (((STAFF.EmailSource) Not Like "10/6/2006" And (STAFF.EmailSource) Not Like "not outlook" And (STAFF.EmailSource) Not Like "checked*" And (STAFF. EmailSource) Not Like "NIG*") AND ((Galdump.Indication) Is Null) AND ((Left( [galdump].[surname],5))=Left([staff].[st_last_nm],5))) OR (((STAFF. EmailSource) Is Null) AND ((Galdump.Indication) Is Null) AND ((Left([galdump]. [surname],5))=Left([staff].[st_last_nm],5))); Whereever the date, I need to put an expression: [forms]! [frmExtractInformation]![txtDate]. But this doesn't work. Can you help me find the correct expression that needs to be inserted next "Not Like"? Please note that the field that contains the date is a "text" data type, not a "date/time" date type. If you need more explanation, please let me know. Thank you. |
#3
|
|||
|
|||
query expression using form
can you look at the query in Design view? or are you only able to look at it
in SQL view? in design View you should be able to put: [forms]![frmExtractInformation].[txtDate] in the Criteria field of the appropriate column field. Your table underlying must also have the Date column as text - not date format - and your Form must still be opened when you run the query so that it can find that parameter..... -- NTC "pjacob" wrote: I have an unbound text box in one of the forms. In the text box, the user enters a date. Once the date is entered, the query needs to pick that date from the form. Right now, I did an update query using a dumpy date. Here is the SQL statement: UPDATE Galdump INNER JOIN (STAFF INNER JOIN STAFF_PHONE_NUMBERS ON STAFF. ST_ID = STAFF_PHONE_NUMBERS.ST_ID) ON (Galdump.country = STAFF_PHONE_NUMBERS. GALCountry) AND (Galdump.forename = STAFF_PHONE_NUMBERS.ST_FIRST_NM) AND (Galdump.city = STAFF_PHONE_NUMBERS.Location) SET STAFF.EmailSource = "10/6/2006", Galdump.Indication = "Y-Match First Name & Country & City", STAFF.st_email = Galdump.mailaddress WHERE (((STAFF.EmailSource) Not Like "10/6/2006" And (STAFF.EmailSource) Not Like "not outlook" And (STAFF.EmailSource) Not Like "checked*" And (STAFF. EmailSource) Not Like "NIG*") AND ((Galdump.Indication) Is Null) AND ((Left( [galdump].[surname],5))=Left([staff].[st_last_nm],5))) OR (((STAFF. EmailSource) Is Null) AND ((Galdump.Indication) Is Null) AND ((Left([galdump]. [surname],5))=Left([staff].[st_last_nm],5))); Whereever the date, I need to put an expression: [forms]! [frmExtractInformation]![txtDate]. But this doesn't work. Can you help me find the correct expression that needs to be inserted next "Not Like"? Please note that the field that contains the date is a "text" data type, not a "date/time" date type. If you need more explanation, please let me know. Thank you. |
#4
|
|||
|
|||
query expression using form
In the Query Design View - not SQL view - you should be able to put:
[forms]![frmExtractInformation].[txtDate] in the Criteria field for the appropriate column - presuming that the underlying table has this field also as a text field. If the table has it as a date field there is a data mismatch type and it may not work.... -- NTC "pjacob" wrote: I have an unbound text box in one of the forms. In the text box, the user enters a date. Once the date is entered, the query needs to pick that date from the form. Right now, I did an update query using a dumpy date. Here is the SQL statement: UPDATE Galdump INNER JOIN (STAFF INNER JOIN STAFF_PHONE_NUMBERS ON STAFF. ST_ID = STAFF_PHONE_NUMBERS.ST_ID) ON (Galdump.country = STAFF_PHONE_NUMBERS. GALCountry) AND (Galdump.forename = STAFF_PHONE_NUMBERS.ST_FIRST_NM) AND (Galdump.city = STAFF_PHONE_NUMBERS.Location) SET STAFF.EmailSource = "10/6/2006", Galdump.Indication = "Y-Match First Name & Country & City", STAFF.st_email = Galdump.mailaddress WHERE (((STAFF.EmailSource) Not Like "10/6/2006" And (STAFF.EmailSource) Not Like "not outlook" And (STAFF.EmailSource) Not Like "checked*" And (STAFF. EmailSource) Not Like "NIG*") AND ((Galdump.Indication) Is Null) AND ((Left( [galdump].[surname],5))=Left([staff].[st_last_nm],5))) OR (((STAFF. EmailSource) Is Null) AND ((Galdump.Indication) Is Null) AND ((Left([galdump]. [surname],5))=Left([staff].[st_last_nm],5))); Whereever the date, I need to put an expression: [forms]! [frmExtractInformation]![txtDate]. But this doesn't work. Can you help me find the correct expression that needs to be inserted next "Not Like"? Please note that the field that contains the date is a "text" data type, not a "date/time" date type. If you need more explanation, please let me know. Thank you. |
#5
|
|||
|
|||
query expression using form
test
-- NTC "pjacob" wrote: I have an unbound text box in one of the forms. In the text box, the user enters a date. Once the date is entered, the query needs to pick that date from the form. Right now, I did an update query using a dumpy date. Here is the SQL statement: UPDATE Galdump INNER JOIN (STAFF INNER JOIN STAFF_PHONE_NUMBERS ON STAFF. ST_ID = STAFF_PHONE_NUMBERS.ST_ID) ON (Galdump.country = STAFF_PHONE_NUMBERS. GALCountry) AND (Galdump.forename = STAFF_PHONE_NUMBERS.ST_FIRST_NM) AND (Galdump.city = STAFF_PHONE_NUMBERS.Location) SET STAFF.EmailSource = "10/6/2006", Galdump.Indication = "Y-Match First Name & Country & City", STAFF.st_email = Galdump.mailaddress WHERE (((STAFF.EmailSource) Not Like "10/6/2006" And (STAFF.EmailSource) Not Like "not outlook" And (STAFF.EmailSource) Not Like "checked*" And (STAFF. EmailSource) Not Like "NIG*") AND ((Galdump.Indication) Is Null) AND ((Left( [galdump].[surname],5))=Left([staff].[st_last_nm],5))) OR (((STAFF. EmailSource) Is Null) AND ((Galdump.Indication) Is Null) AND ((Left([galdump]. [surname],5))=Left([staff].[st_last_nm],5))); Whereever the date, I need to put an expression: [forms]! [frmExtractInformation]![txtDate]. But this doesn't work. Can you help me find the correct expression that needs to be inserted next "Not Like"? Please note that the field that contains the date is a "text" data type, not a "date/time" date type. If you need more explanation, please let me know. Thank you. |
#6
|
|||
|
|||
query expression using form
I have the form opened, but the query does not pick the date from the form
when I run the query. If I just run the query without the form opened and it ask for the date in the parameter box. After I insert the date, the query shows the right record. But it does't pick the date when I have the form opened. Something in the form is not represented correctly. Can you please help! Thanks. NetworkTrade wrote: can you look at the query in Design view? or are you only able to look at it in SQL view? in design View you should be able to put: [forms]![frmExtractInformation].[txtDate] in the Criteria field of the appropriate column field. Your table underlying must also have the Date column as text - not date format - and your Form must still be opened when you run the query so that it can find that parameter..... I have an unbound text box in one of the forms. In the text box, the user enters a date. Once the date is entered, the query needs to pick that date [quoted text clipped - 25 lines] Thank you. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200610/1 |
Thread Tools | |
Display Modes | |
|
|