textbox and query

[Kevin]

Hi
I have the code below that allows me to test the result of a query
The query is a crosstab and pivots on months
what i want to do is check if the field december is different to blank
and if so increment the textbox.
the problem is
1. if no value for december the field is not included in the result
2. assuming i can get over 1st problem how can i check if field
december is different to space?

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Set db = CurrentDb
Set qd = db.QueryDefs("monthly_totals_team")
Set rs = qd.OpenRecordset()

If Not rs.EOF <> 0 Then
Me.Cboyears = Me.Cboyears + 1
End If
On Error Resume Next
Set rs = Nothing
Set qd = Nothing
Set db = Nothing
thanks

kevin

[Justin Hoffman]

"Kevin" <ke******@btinternet.com> wrote in message
news:9f**************************@posting.google.c om...

Hi
I have the code below that allows me to test the result of a query
The query is a crosstab and pivots on months
what i want to do is check if the field december is different to blank
and if so increment the textbox.
the problem is
1. if no value for december the field is not included in the result
2. assuming i can get over 1st problem how can i check if field
december is different to space?

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Set db = CurrentDb
Set qd = db.QueryDefs("monthly_totals_team")
Set rs = qd.OpenRecordset()

If Not rs.EOF <> 0 Then
Me.Cboyears = Me.Cboyears + 1
End If
On Error Resume Next
Set rs = Nothing
Set qd = Nothing
Set db = Nothing
thanks

kevin

We can't see the SQL for the query, so do you mean that "December" might, or
might not, be a column heading? If so, what row do you need to locate, or
is there only one?
In general, you could use the .FindFirst method of the recordset if there is
more than one row, then check for the .NoMatch condition to see if you have
located the correct row. Once you have the right row, you can evaluate
Len(Trim(Nz(rst.Fields("December"),""))) to see whether it is null, blank or
a space. Of course if the column December does not exist, you have to catch
the error 3265 Item not found in this collection.

[Arno R]

Kevin,
If I do understand your question correctly, then I think you need to 'force' your columns
to appear in the resultset.
How? Just put the ColumnHeadings in the properties of your query.
In your sql it would look like:
Transform .... Select .... FROM .... GROUPBY .....PIVOT Month In("jan","feb",
......,"dec");

--
Hope this helps
Arno R
"Kevin" <ke******@btinternet.com> schreef in bericht
news:9f**************************@posting.google.c om...

Hi
I have the code below that allows me to test the result of a query
The query is a crosstab and pivots on months
what i want to do is check if the field december is different to blank
and if so increment the textbox.
the problem is
1. if no value for december the field is not included in the result
2. assuming i can get over 1st problem how can i check if field
december is different to space?

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Set db = CurrentDb
Set qd = db.QueryDefs("monthly_totals_team")
Set rs = qd.OpenRecordset()

If Not rs.EOF <> 0 Then
Me.Cboyears = Me.Cboyears + 1
End If
On Error Resume Next
Set rs = Nothing
Set qd = Nothing
Set db = Nothing
thanks

kevin