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.

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.

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.

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.

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.

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