Problem in event procedure - help please!

[Phil Hood]

Hi,

Can anyone suggest why the following code generates the
error: "Can't find the field 'Match' referred to in your
expression"

strQuerySQL2 = "SELECT Results.RiderID FROM Match INNER
JOIN Results ON Match.MatchID = Results.MatchID WHERE
(((Results.RiderID)= " & [Forms].[add team details].
[RiderID] & ") AND 2004 = " & DatePart("yyyy", [Match]!
[MatchDate]) & ";"

I'm stumped. Help please!

Thanks Phil.

 

[Wayne Morgan]

DatePart("yyyy", [Match]![MatchDate])

If you are referring to a control on another form called Match. The actual
syntax should be

Forms![Match]![MatchDate]

If you are referring to the table called Match and you want the value from
the MatchDate field, you will need to do something to also tell it which
record to get the value from. Are you wanting to pass the DatePart statement
as part of the SQL or do you just want to pass the value computed by the
DatePart statement as part of the SQL (i.e. should the final SQL still say
DatePart... or should it just say 2005)? Also, it appears that you have a
field called 2004. Could it just be that you have this part typed in
reversed? If so, to put the DatePart statement inside the quotation marks,
you'll need to use single quotes instead of double quotes around 'yyyy'. In
other words, should this be:

") AND 2004 = " & DatePart("yyyy", [Match]![MatchDate]) & ";"<<

") AND DatePart('yyyy', [Match].[MatchDate]) = 2004;"


--
Wayne Morgan
MS Access MVP

Hi,

Can anyone suggest why the following code generates the
error: "Can't find the field 'Match' referred to in your
expression"

strQuerySQL2 = "SELECT Results.RiderID FROM Match INNER
JOIN Results ON Match.MatchID = Results.MatchID WHERE
(((Results.RiderID)= " & [Forms].[add team details].
[RiderID] & ") AND 2004 = " & DatePart("yyyy", [Match]!
[MatchDate]) & ";"

I'm stumped. Help please!

Thanks Phil.

 

[Guest]

Looks like you have one of your table field references expressed as a form
control:

....AND 2004 = " & DatePart("yyyy", Match.
MatchDate) & ";"

TomU

 

[Phil Hood]

Hi,

Thanks for your help. I'm not clear what you mean in all
cases but here's the answer to your questions.

"If you are referring to the table called Match and you
want the value from the MatchDate field"

I am referring to a table called Match that has a field
called MatchDate.

What I am trying to do is select records from a Results
table that meet the following criteria:

RiderID (on form Add Team Details) = RiderID (in the
Results table)
AND
MatchDate (in the Match table) = 2004


I'm using PartDate because MatchDate is DD/MM/YYYY.


The SQL was my attempt to do this.

Help please!

Thanks Phil.

-----Original Message-----
DatePart("yyyy", [Match]![MatchDate])

If you are referring to a control on another form called Match. The actual
syntax should be

Forms![Match]![MatchDate]

If you are referring to the table called Match and you want the value from
the MatchDate field, you will need to do something to also tell it which
record to get the value from. Are you wanting to pass the DatePart statement
as part of the SQL or do you just want to pass the value computed by the
DatePart statement as part of the SQL (i.e. should the final SQL still say
DatePart... or should it just say 2005)? Also, it appears that you have a
field called 2004. Could it just be that you have this part typed in
reversed? If so, to put the DatePart statement inside the quotation marks,
you'll need to use single quotes instead of double quotes around 'yyyy'. In
other words, should this be:

") AND 2004 = " & DatePart("yyyy", [Match]!

[MatchDate]) & ";"<<

") AND DatePart('yyyy', [Match].[MatchDate]) = 2004;"


--
Wayne Morgan
MS Access MVP

Hi,

Can anyone suggest why the following code generates the
error: "Can't find the field 'Match' referred to in your
expression"

strQuerySQL2 = "SELECT Results.RiderID FROM Match INNER
JOIN Results ON Match.MatchID = Results.MatchID WHERE
(((Results.RiderID)= " & [Forms].[add team details].
[RiderID] & ") AND 2004 = " & DatePart("yyyy", [Match]!
[MatchDate]) & ";"

I'm stumped. Help please!

Thanks Phil.

.

 

[Wayne Morgan]

Since Match is a table in your query, it's fields should be available to the
query. So, a little modification to what you have should work.

strQuerySQL2 = "SELECT Results.RiderID FROM Match INNER
JOIN Results ON Match.MatchID = Results.MatchID WHERE
(((Results.RiderID)= " & [Forms].[add team details].
[RiderID] & ") AND DatePart('yyyy', [Match].[MatchDate]) = 2004;"

Also, the way you have it, RiderID should be a number value. If it is text,
a little more modification will be needed. Is the "Add Team Details" form a
subform of another form or is it by itself? Also, the way you have RiderID
entered, the value is being assigned when you create strQuerySQL2, not when
the query is used.

You may want to temporarily add the line

Debug.Print strQuerySQL2

right after the line above and see what is actually happening. This will
cause the actual value of the variable to be displayed in the Immediate
Window.

--
Wayne Morgan
MS Access MVP

Hi,

Thanks for your help. I'm not clear what you mean in all
cases but here's the answer to your questions.

"If you are referring to the table called Match and you
want the value from the MatchDate field"

I am referring to a table called Match that has a field
called MatchDate.

What I am trying to do is select records from a Results
table that meet the following criteria:

RiderID (on form Add Team Details) = RiderID (in the
Results table)
AND
MatchDate (in the Match table) = 2004


I'm using PartDate because MatchDate is DD/MM/YYYY.


The SQL was my attempt to do this.

Help please!

Thanks Phil.

-----Original Message-----
DatePart("yyyy", [Match]![MatchDate])

If you are referring to a control on another form called Match. The actual
syntax should be

Forms![Match]![MatchDate]

If you are referring to the table called Match and you want the value from
the MatchDate field, you will need to do something to also tell it which
record to get the value from. Are you wanting to pass the DatePart statement
as part of the SQL or do you just want to pass the value computed by the
DatePart statement as part of the SQL (i.e. should the final SQL still say
DatePart... or should it just say 2005)? Also, it appears that you have a
field called 2004. Could it just be that you have this part typed in
reversed? If so, to put the DatePart statement inside the quotation marks,
you'll need to use single quotes instead of double quotes around 'yyyy'. In
other words, should this be:

") AND 2004 = " & DatePart("yyyy", [Match]!

[MatchDate]) & ";"<<

") AND DatePart('yyyy', [Match].[MatchDate]) = 2004;"


--
Wayne Morgan
MS Access MVP

Hi,

Can anyone suggest why the following code generates the
error: "Can't find the field 'Match' referred to in your
expression"

strQuerySQL2 = "SELECT Results.RiderID FROM Match INNER
JOIN Results ON Match.MatchID = Results.MatchID WHERE
(((Results.RiderID)= " & [Forms].[add team details].
[RiderID] & ") AND 2004 = " & DatePart("yyyy", [Match]!
[MatchDate]) & ";"

I'm stumped. Help please!

Thanks Phil.

.