can someone help me write this statement

L

lilbit27

strSQL = "TARA.*, Year[billeffdte] & Month[billeffdte] AS
EffDateCompare FROM TARA. Year[billeffdte])&
Month[billeffdte]=YearDateAdd'm',-2,[rptdt]& MonthDateAdd'm',-2,
[rptdt]"
 
J

John Vinson

strSQL = "TARA.*, Year[billeffdte] & Month[billeffdte] AS
EffDateCompare FROM TARA. Year[billeffdte])&
Month[billeffdte]=YearDateAdd'm',-2,[rptdt]& MonthDateAdd'm',-2,
[rptdt]"
Click to expand...

It might help if you were to describe what you're trying to
accomplish. This is complete gibberish as far as SQL is concerned;
"TARA." is not valid SQL in any sense; you're using [] - which
delimits field or tablenames - where you should be using () - which
delimits function arguments; you have some meaningless expressions
YearDateAdd and MonthDateAdd...

Could you please explain what you have in the table, and what you want
this SQL string to return or accomplish???

John W. Vinson[MVP]
 
L

lilbit27

strSQL = "TARA.*, Year[billeffdte] & Month[billeffdte] AS
EffDateCompare FROM TARA. Year[billeffdte])&
Month[billeffdte]=YearDateAdd'm',-2,[rptdt]& MonthDateAdd'm',-2,
[rptdt]"
Click to expand...

It might help if you were to describe what you're trying to
accomplish. This is complete gibberish as far as SQL is concerned;
"TARA." is not valid SQL in any sense; you're using [] - which
delimits field or tablenames - where you should be using () - which
delimits function arguments; you have some meaningless expressions
YearDateAdd and MonthDateAdd...

Could you please explain what you have in the table, and what you want
this SQL string to return or accomplish???

John W. Vinson[MVP]
Click to expand...

tara is the name of my table and the is what I have in the qry:

Field TARA.*
Field NetC criteria = 0
Field PendDte criteria Is null
Field EffDateCompare: Year([billeffdte]) & Month([billeffdte])
Criteria: Year(DateAdd("m",-2,[rptdt])) & Month(DateAdd("m",-2,
[rptdt]))

And I went to view the sql stament and it said:

SELECT TARA.*, Year([billeffdte]) & Month([billeffdte]) AS
EffDateCompare
FROM TARA
WHERE (((TARA.NetC)=0) AND ((TARA.PendDte) Is Null) AND
((Year([billeffdte]) & Month([billeffdte]))=Year(DateAdd("m",-2,
[rptdt])) & Month(DateAdd("m",-2,[rptdt]))));


What I am trying do do is write the above statement in the after
update of an option button on a form so that it will filter my
subform.

Hope this makes more sense to you.
 
J

John Vinson

tara is the name of my table and the is what I have in the qry:

Field TARA.*
Field NetC criteria = 0
Field PendDte criteria Is null
Field EffDateCompare: Year([billeffdte]) & Month([billeffdte])
Criteria: Year(DateAdd("m",-2,[rptdt])) & Month(DateAdd("m",-2,
[rptdt]))

And I went to view the sql stament and it said:

SELECT TARA.*, Year([billeffdte]) & Month([billeffdte]) AS
EffDateCompare
FROM TARA
WHERE (((TARA.NetC)=0) AND ((TARA.PendDte) Is Null) AND
((Year([billeffdte]) & Month([billeffdte]))=Year(DateAdd("m",-2,
[rptdt])) & Month(DateAdd("m",-2,[rptdt]))));


What I am trying do do is write the above statement in the after
update of an option button on a form so that it will filter my
Click to expand...

If you're trying to filter by a date field - you *don't* need or want
to construct a text string concatenating years and months. Instead,
use the DateSerial() function to construct a date/time field.

If you want to filter the subform to return any date during the month
two months prior to [rptdt], try
= DateSerial(Year([rptdt]), Month([rptdt]), 1) AND < DateSerial(Year([rptdt]), Month([rptdt]) + 1, 1)
Click to expand...

as a criterion on billeffdte.

John W. Vinson[MVP]
 
L

lilbit27

tara is the name of my table and the is what I have in the qry:
Click to expand...
Field TARA.*
Field NetC criteria = 0
Field PendDte criteria Is null
Field EffDateCompare: Year([billeffdte]) & Month([billeffdte])
Criteria: Year(DateAdd("m",-2,[rptdt])) & Month(DateAdd("m",-2,
[rptdt]))
Click to expand...
And I went to view the sql stament and it said:
Click to expand...
SELECT TARA.*, Year([billeffdte]) & Month([billeffdte]) AS
EffDateCompare
FROM TARA
WHERE (((TARA.NetC)=0) AND ((TARA.PendDte) Is Null) AND
((Year([billeffdte]) & Month([billeffdte]))=Year(DateAdd("m",-2,
[rptdt])) & Month(DateAdd("m",-2,[rptdt]))));
Click to expand...
What I am trying do do is write the above statement in the after
update of an option button on a form so that it will filter my
Click to expand...

If you're trying to filter by a date field - you *don't* need or want
to construct a text string concatenating years and months. Instead,
use the DateSerial() function to construct a date/time field.

If you want to filter the subform to return any date during the month
two months prior to [rptdt], try
= DateSerial(Year([rptdt]), Month([rptdt]), 1) AND < DateSerial(Year([rptdt]), Month([rptdt]) + 1, 1)
Click to expand...

as a criterion on billeffdte.

John W. Vinson[MVP] - Hide quoted text -

- Show quoted text -
Click to expand...

i tried this and I come back will a null value when I run the query.
 
L

lilbit27

tara is the name of my table and the is what I have in the qry:
Click to expand...
Field TARA.*
Field NetC criteria = 0
Field PendDte criteria Is null
Field EffDateCompare: Year([billeffdte]) & Month([billeffdte])
Criteria: Year(DateAdd("m",-2,[rptdt])) & Month(DateAdd("m",-2,
[rptdt]))
Click to expand...
And I went to view the sql stament and it said:
Click to expand...
SELECT TARA.*, Year([billeffdte]) & Month([billeffdte]) AS
EffDateCompare
FROM TARA
WHERE (((TARA.NetC)=0) AND ((TARA.PendDte) Is Null) AND
((Year([billeffdte]) & Month([billeffdte]))=Year(DateAdd("m",-2,
[rptdt])) & Month(DateAdd("m",-2,[rptdt]))));
Click to expand...
What I am trying do do is write the above statement in the after
update of an option button on a form so that it will filter my
Click to expand...

If you're trying to filter by a date field - you *don't* need or want
to construct a text string concatenating years and months. Instead,
use the DateSerial() function to construct a date/time field.

If you want to filter the subform to return any date during the month
two months prior to [rptdt], try
= DateSerial(Year([rptdt]), Month([rptdt]), 1) AND < DateSerial(Year([rptdt]), Month([rptdt]) + 1, 1)
Click to expand...

as a criterion on billeffdte.

John W. Vinson[MVP] - Hide quoted text -

- Show quoted text -
Click to expand...

Now how do I code this in the after update of an option group.

Here is the case statement:
Case 4
ittype = 4
'60 Day Items
Me.Cmdclose.SetFocus
Me.FPastDues.Form.FilterOn = False
strSQL = "SELECT TARA.NetC=0 AND TARA.PendDte Is Null AND
TARA .BillEffDte>=DateSerial(Year([rptdt]),Month([rptdt])-2,1) And
TARA.BillEffDte<=DateSerial(Year([rptdt]),Month([rptdt])-1,0)"
FPastDues.Visible = True
Me.FPastDues.Form.Filter = strSQL
Me.FPastDues.Form.FilterOn = True
End Select

End sub
 
D

Douglas J. Steele

A Filter doesn't have SELECT in it. It's the WHERE clause of a SQL
statement, without the word WHERE in it.

Try:

strSQL = "TARA.NetC=0 AND TARA.PendDte Is Null AND " & _
"TARA .BillEffDte>=DateSerial(Year([rptdt]),Month([rptdt])-2,1) And " & _
"TARA.BillEffDte<=DateSerial(Year([rptdt]),Month([rptdt])-1,0)"

Actually, since field names must be unique in the SQL, you can get by
without the table name:

strSQL = "NetC=0 AND PendDte Is Null AND " & _
"BillEffDte>=DateSerial(Year([rptdt]),Month([rptdt])-2,1) And " & _
"BillEffDte<=DateSerial(Year([rptdt]),Month([rptdt])-1,0)"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


lilbit27 said:
tara is the name of my table and the is what I have in the qry:
Click to expand...
Field TARA.*
Field NetC criteria = 0
Field PendDte criteria Is null
Field EffDateCompare: Year([billeffdte]) & Month([billeffdte])
Criteria: Year(DateAdd("m",-2,[rptdt])) & Month(DateAdd("m",-2,
[rptdt]))
Click to expand...
And I went to view the sql stament and it said:
Click to expand...
SELECT TARA.*, Year([billeffdte]) & Month([billeffdte]) AS
EffDateCompare
FROM TARA
WHERE (((TARA.NetC)=0) AND ((TARA.PendDte) Is Null) AND
((Year([billeffdte]) & Month([billeffdte]))=Year(DateAdd("m",-2,
[rptdt])) & Month(DateAdd("m",-2,[rptdt]))));
Click to expand...
What I am trying do do is write the above statement in the after
update of an option button on a form so that it will filter my
Click to expand...

If you're trying to filter by a date field - you *don't* need or want
to construct a text string concatenating years and months. Instead,
use the DateSerial() function to construct a date/time field.

If you want to filter the subform to return any date during the month
two months prior to [rptdt], try
= DateSerial(Year([rptdt]), Month([rptdt]), 1) AND <
DateSerial(Year([rptdt]), Month([rptdt]) + 1, 1)
Click to expand...

as a criterion on billeffdte.

John W. Vinson[MVP] - Hide quoted text -

- Show quoted text -
Click to expand...

Now how do I code this in the after update of an option group.

Here is the case statement:
Case 4
ittype = 4
'60 Day Items
Me.Cmdclose.SetFocus
Me.FPastDues.Form.FilterOn = False
strSQL = "SELECT TARA.NetC=0 AND TARA.PendDte Is Null AND
TARA .BillEffDte>=DateSerial(Year([rptdt]),Month([rptdt])-2,1) And
TARA.BillEffDte<=DateSerial(Year([rptdt]),Month([rptdt])-1,0)"
FPastDues.Visible = True
Me.FPastDues.Form.Filter = strSQL
Me.FPastDues.Form.FilterOn = True
End Select

End sub
Click to expand...
 
L

lilbit27

A Filter doesn't have SELECT in it. It's the WHERE clause of a SQL
statement, without the word WHERE in it.

Try:

strSQL = "TARA.NetC=0 AND TARA.PendDte Is Null AND " & _
"TARA .BillEffDte>=DateSerial(Year([rptdt]),Month([rptdt])-2,1) And " & _
"TARA.BillEffDte<=DateSerial(Year([rptdt]),Month([rptdt])-1,0)"

Actually, since field names must be unique in the SQL, you can get by
without the table name:

strSQL = "NetC=0 AND PendDte Is Null AND " & _
"BillEffDte>=DateSerial(Year([rptdt]),Month([rptdt])-2,1) And " & _
"BillEffDte<=DateSerial(Year([rptdt]),Month([rptdt])-1,0)"

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)




On 3 Feb 2007 10:11:43 -0800, "lilbit27" <[email protected]>
wrote:
tara is the name of my table and the is what I have in the qry:
Field TARA.*
Field NetC criteria = 0
Field PendDte criteria Is null
Field EffDateCompare: Year([billeffdte]) & Month([billeffdte])
Criteria: Year(DateAdd("m",-2,[rptdt])) & Month(DateAdd("m",-2,
[rptdt]))
And I went to view the sql stament and it said:
SELECT TARA.*, Year([billeffdte]) & Month([billeffdte]) AS
EffDateCompare
FROM TARA
WHERE (((TARA.NetC)=0) AND ((TARA.PendDte) Is Null) AND
((Year([billeffdte]) & Month([billeffdte]))=Year(DateAdd("m",-2,
[rptdt])) & Month(DateAdd("m",-2,[rptdt]))));
What I am trying do do is write the above statement in the after
update of an option button on a form so that it will filter my
If you're trying to filter by a date field - you *don't* need or want
to construct a text string concatenating years and months. Instead,
use the DateSerial() function to construct a date/time field.
If you want to filter the subform to return any date during the month
two months prior to [rptdt], try
= DateSerial(Year([rptdt]), Month([rptdt]), 1) AND <
DateSerial(Year([rptdt]), Month([rptdt]) + 1, 1)
as a criterion on billeffdte.
John W. Vinson[MVP] - Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
Now how do I code this in the after update of an option group.
Click to expand...
Here is the case statement:
Case 4
ittype = 4
'60 Day Items
Me.Cmdclose.SetFocus
Me.FPastDues.Form.FilterOn = False
strSQL = "SELECT TARA.NetC=0 AND TARA.PendDte Is Null AND
TARA .BillEffDte>=DateSerial(Year([rptdt]),Month([rptdt])-2,1) And
TARA.BillEffDte<=DateSerial(Year([rptdt]),Month([rptdt])-1,0)"
FPastDues.Visible = True
Me.FPastDues.Form.Filter = strSQL
Me.FPastDues.Form.FilterOn = True
End Select
Click to expand...
End sub- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

I put this and i got and no record error:
Case 4
ittype = 4
'60 Day Items
Me.Cmdclose.SetFocus
Me.FPastDues.Form.FilterOn = False
strSQL = "TARA.NetC=0 AND TARA.PendDte Is Null AND " &
"TARA .BillEffDte>=DateSerial(Year([rptdt]),Month([rptdt])-2,1) And "
& "TARA.BillEffDte<=DateSerial(Year([rptdt]),Month([rptdt])-1,0)"
FPastDues.Visible = True
Me.FPastDues.Form.Filter = strSQL
Me.FPastDues.Form.FilterOn = True
end select.

I ge tthe no record error.
 
L

lilbit27

A Filter doesn't have SELECT in it. It's the WHERE clause of a SQL
statement, without the word WHERE in it.

Try:

strSQL = "TARA.NetC=0 AND TARA.PendDte Is Null AND " & _
"TARA .BillEffDte>=DateSerial(Year([rptdt]),Month([rptdt])-2,1) And " & _
"TARA.BillEffDte<=DateSerial(Year([rptdt]),Month([rptdt])-1,0)"

Actually, since field names must be unique in the SQL, you can get by
without the table name:

strSQL = "NetC=0 AND PendDte Is Null AND " & _
"BillEffDte>=DateSerial(Year([rptdt]),Month([rptdt])-2,1) And " & _
"BillEffDte<=DateSerial(Year([rptdt]),Month([rptdt])-1,0)"

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)




On 3 Feb 2007 10:11:43 -0800, "lilbit27" <[email protected]>
wrote:
tara is the name of my table and the is what I have in the qry:
Field TARA.*
Field NetC criteria = 0
Field PendDte criteria Is null
Field EffDateCompare: Year([billeffdte]) & Month([billeffdte])
Criteria: Year(DateAdd("m",-2,[rptdt])) & Month(DateAdd("m",-2,
[rptdt]))
And I went to view the sql stament and it said:
SELECT TARA.*, Year([billeffdte]) & Month([billeffdte]) AS
EffDateCompare
FROM TARA
WHERE (((TARA.NetC)=0) AND ((TARA.PendDte) Is Null) AND
((Year([billeffdte]) & Month([billeffdte]))=Year(DateAdd("m",-2,
[rptdt])) & Month(DateAdd("m",-2,[rptdt]))));
What I am trying do do is write the above statement in the after
update of an option button on a form so that it will filter my
If you're trying to filter by a date field - you *don't* need or want
to construct a text string concatenating years and months. Instead,
use the DateSerial() function to construct a date/time field.
If you want to filter the subform to return any date during the month
two months prior to [rptdt], try
= DateSerial(Year([rptdt]), Month([rptdt]), 1) AND <
DateSerial(Year([rptdt]), Month([rptdt]) + 1, 1)
as a criterion on billeffdte.
John W. Vinson[MVP] - Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
Now how do I code this in the after update of an option group.
Click to expand...
Here is the case statement:
Case 4
ittype = 4
'60 Day Items
Me.Cmdclose.SetFocus
Me.FPastDues.Form.FilterOn = False
strSQL = "SELECT TARA.NetC=0 AND TARA.PendDte Is Null AND
TARA .BillEffDte>=DateSerial(Year([rptdt]),Month([rptdt])-2,1) And
TARA.BillEffDte<=DateSerial(Year([rptdt]),Month([rptdt])-1,0)"
FPastDues.Visible = True
Me.FPastDues.Form.Filter = strSQL
Me.FPastDues.Form.FilterOn = True
End Select
Click to expand...
End sub- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

never mind I got it. I took the _ out but the the "&"


Thanks for your help.
 
Top