Using a year from a text box in a qurey

  • Thread starter Thread starter Paul B
  • Start date Start date
P

Paul B

New to using access so please be gentle :)

Criteria in query
Between #1/1/06# And #12/31/06#

I Have a form named frmYear with a text box named tbYear

How can I use the value from the text box for the year

Something like this but it does not work, or is there a better way to do
this?
Between #1/1/[Forms]![frmYear]![tbYear]# And
#12/31/[Forms]![frmYear]![tbYear]#

using access 2002
Thanks
 
Dorian, I am new to Access, I do not know how to use that, could you please
explain it a little more?


Thanks

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

mscertified said:
Try:

WHERE Datepart("yyyy",tabledate) = [Forms]![frmYear]![tbYear]

-Dorian

Paul B said:
New to using access so please be gentle :)

Criteria in query
Between #1/1/06# And #12/31/06#

I Have a form named frmYear with a text box named tbYear

How can I use the value from the text box for the year

Something like this but it does not work, or is there a better way to do
this?
Between #1/1/[Forms]![frmYear]![tbYear]# And
#12/31/[Forms]![frmYear]![tbYear]#

using access 2002
Thanks
Click to expand...
Click to expand...
 
You are probably using the query builder - which I never use. I always code
in raw SQL. Go into query builder on your existing query they click 'close'
and then click 'sql' in your toolbar somewhere. Then change your WHERE
statement in the SQL to what I gave substituting the name of your column
containing the date.

-Dorian

Paul B said:
Dorian, I am new to Access, I do not know how to use that, could you please
explain it a little more?


Thanks

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

mscertified said:
Try:

WHERE Datepart("yyyy",tabledate) = [Forms]![frmYear]![tbYear]

-Dorian

Paul B said:
New to using access so please be gentle :)

Criteria in query
Between #1/1/06# And #12/31/06#

I Have a form named frmYear with a text box named tbYear

How can I use the value from the text box for the year

Something like this but it does not work, or is there a better way to do
this?
Between #1/1/[Forms]![frmYear]![tbYear]# And
#12/31/[Forms]![frmYear]![tbYear]#

using access 2002
Thanks
Click to expand...
Click to expand...
Click to expand...
 
New to using access so please be gentle :)

Criteria in query
Between #1/1/06# And #12/31/06#

I Have a form named frmYear with a text box named tbYear

How can I use the value from the text box for the year

Something like this but it does not work, or is there a better way to do
this?
Between #1/1/[Forms]![frmYear]![tbYear]# And
#12/31/[Forms]![frmYear]![tbYear]#
Try

= DateSerial([Forms]![frmYear]![tbYear], 1, 1) AND < DateSerial([Forms]![frmYear]![tbYear] + 1, 1, 1)
Click to expand...

John W. Vinson[MVP]
 
Dorian, in SQL view this is what I get, tried put in what you gave me for
the between part but I keep getting a syntax error

SELECT ROOT_AE_P_WKA_D.PROPOSAL AS [Request #], ROOT_AE_P_WKA_E.SCHED_DATE
AS [Date], ROOT_AE_P_WKA_E.SHOP_PERSON AS [Person ID], ROOT_AE_P_WKA_E.SHOP
AS [Shop #]
FROM (ROOT_AE_P_WKA_D INNER JOIN ROOT_AE_P_WKA_E ON
(ROOT_AE_P_WKA_D.SHOP_PERSON = ROOT_AE_P_WKA_E.SHOP_PERSON) AND
(ROOT_AE_P_WKA_D.TRANS_NO = ROOT_AE_P_WKA_E.TRANS_NO)) INNER JOIN
ROOT_AE_L_MAN_E ON ROOT_AE_P_WKA_D.SHOP_PERSON = ROOT_AE_L_MAN_E.SHOP_PERSON
WHERE (((ROOT_AE_P_WKA_D.PROPOSAL)<>"0700229" And
(ROOT_AE_P_WKA_D.PROPOSAL)<>"0702302") AND ((ROOT_AE_P_WKA_E.SCHED_DATE)
Between #1/1/2006# And #12/31/2006#) AND
((ROOT_AE_P_WKA_E.SHOP_PERSON)<>"JPC082702") AND
((ROOT_AE_P_WKA_E.SHOP)="56"));


?????


mscertified said:
You are probably using the query builder - which I never use. I always code
in raw SQL. Go into query builder on your existing query they click 'close'
and then click 'sql' in your toolbar somewhere. Then change your WHERE
statement in the SQL to what I gave substituting the name of your column
containing the date.

-Dorian

Paul B said:
Dorian, I am new to Access, I do not know how to use that, could you please
explain it a little more?


Thanks

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

mscertified said:
Try:

WHERE Datepart("yyyy",tabledate) = [Forms]![frmYear]![tbYear]

-Dorian

:

New to using access so please be gentle :)

Criteria in query
Between #1/1/06# And #12/31/06#

I Have a form named frmYear with a text box named tbYear

How can I use the value from the text box for the year

Something like this but it does not work, or is there a better way to do
this?
Between #1/1/[Forms]![frmYear]![tbYear]# And
#12/31/[Forms]![frmYear]![tbYear]#

using access 2002
Thanks
Click to expand...
Click to expand...
Click to expand...
 
John, this looks like it is working, will test it out at work,
Thanks


John Vinson said:
New to using access so please be gentle :)

Criteria in query
Between #1/1/06# And #12/31/06#

I Have a form named frmYear with a text box named tbYear

How can I use the value from the text box for the year

Something like this but it does not work, or is there a better way to do
this?
Between #1/1/[Forms]![frmYear]![tbYear]# And
#12/31/[Forms]![frmYear]![tbYear]#
Try

= DateSerial([Forms]![frmYear]![tbYear], 1, 1) AND <
Click to expand...
Click to expand...
DateSerial([Forms]![frmYear]![tbYear] + 1, 1, 1)
 
In the criteria under the date use

Between DateSerial([Forms]![frmYear]![tbYear],1,1) and
DateSerial([Forms]![frmYear]![tbYear],12,31)

For this to work, the Form frmYear must be open so the query can "see" the
form and the value in the control.
 
John, thanks


John Spencer said:
In the criteria under the date use

Between DateSerial([Forms]![frmYear]![tbYear],1,1) and
DateSerial([Forms]![frmYear]![tbYear],12,31)

For this to work, the Form frmYear must be open so the query can "see" the
form and the value in the control.


Paul B said:
New to using access so please be gentle :)

Criteria in query
Between #1/1/06# And #12/31/06#

I Have a form named frmYear with a text box named tbYear

How can I use the value from the text box for the year

Something like this but it does not work, or is there a better way to do
this?
Between #1/1/[Forms]![frmYear]![tbYear]# And
#12/31/[Forms]![frmYear]![tbYear]#

using access 2002
Thanks
Click to expand...
Click to expand...
 
Back
Top