How to query on user form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is what I have in a table (MyData) and userform has the same objects
TrackNo as text
DateOpen as date (ShortDate)
Cust as text
Reason as Memo

This is the query I have in place (Temp01)
Year: Year([DateOpen])
Month: Month([DateOpen])
HowMany: Count([Year]+[Month])
This is the SQL for HowMany field in the query
SELECT Year([DateOpen]) AS [Year], Month([DateOpen]) AS [Month],
Count([Year]+[Month]) AS HowMany
FROM MyData
GROUP BY Year([DateOpen]), Month([DateOpen]);

This is want I want to do on user form:
Person enters the DateOpen in textbox and this runs the AfterUpdate event.
The event returns HowMany value from the query and I can complete the event
by adding 1 to that value and set the TrackingNo as yymmdd-HowMany,â€000â€

What can you offer to help me with this? I have used Dcount until my head
hurts to do this without the query and still cannot seem to solve this
problem so am now trying to get help with the query method.

Thank you for your assistance.
 
Sgwapt,

[Year]+[Month] for today's date will evaluate to 2006+6=2012, which I
doubt has any meaning in your application, so I suspect you have
misinterpreted something. Nevertheless, Count([Year]+[Month]) will
evaluate to 1 for each record, regardless. I suspect this is not what
you intend. Maybe it would help if you could give some specific
examples of your data, and what you want to happen. So far, I don't
understand HowMany.

A couple of other comments, as an aside...
- 'year' and 'month', being "reserved words" in Access, should not be
used as the names of fields or controls or database objects. I would
recommend renaming these.
- it would appear that you are trying to store a value in a field
which is derived by calculation/manipulation of other data. As a matter
of principle, this is seldom a good idea. Better to keep the elements
of the data "atomic", and calculate/concatenate as required, in a query
or in calculated controls, whenever needed for your purposes on form or
report.
 
Dear Steve,

Here are the details on what I am trying to do with this application.

The query does work as listed below.
This returns the count for the year+month from the date value in the table.
These are the fileds in the query that returns how many entries in the table
based on DateOpen field

Year: Year([DateOpen])
Month: Month([DateOpen])
HowMany: Count([Year]+[Month])

Query Data Results
Year Month HowMany
2005 7 1
2006 7 8
2006 8 3
2006 9 1
2006 10 2

This is the SQL statement created when I make this query.
SELECT Year([DateOpen]) AS [Year], Month([DateOpen]) AS [Month],
Count([Year]+[Month]) AS HowMany
FROM CcnData
GROUP BY Year([DateOpen]), Month([DateOpen]);

This is the data table showing how I want to use this information to create
my TrackNo to be able to see the progress.

If for example the user entered a new entry with the date 7/30/2006 the
AfterUpdate attached to the textbox would return 9 based on there are 8
counts for the year 2006 and month 7 as seen in the query. I then can use
this to create a TrackNo as C0607-09 (Cyymm-TrackNo). I am not sure which is
the best method to use to get this number. Have the query that works but not
sure how to get this value when the DateOpen (textbox) AfterUpdate event
fires on user form. I have used DCount till my head hurts and cannot seem to
figure this out either.

MyData
TrackNo DateOpen Reason
C0507-01 7/5/2005 Test
C0607-01 7/5/2006 Test
C0607-02 7/12/2006 Any
C0607-03 7/13/2006 Test
C0607-04 7/15/2006 Test
C0607-05 7/15/2006
C0607-06 7/25/2006 TEST
C0607-07 7/25/2006
C0607-08 7/29/2006 ANY
C0608-01 8/6/2006
C0608-02 8/15/2006 ANY
C0608-03 8/15/2006
C0609-01 9/15/2006
C0610-01 10/1/2006
C0610-02 10/2/2006

If you need additional information please let me know.

Thank you for your assistance

--
George G


Steve Schapel said:
Sgwapt,

[Year]+[Month] for today's date will evaluate to 2006+6=2012, which I
doubt has any meaning in your application, so I suspect you have
misinterpreted something. Nevertheless, Count([Year]+[Month]) will
evaluate to 1 for each record, regardless. I suspect this is not what
you intend. Maybe it would help if you could give some specific
examples of your data, and what you want to happen. So far, I don't
understand HowMany.

A couple of other comments, as an aside...
- 'year' and 'month', being "reserved words" in Access, should not be
used as the names of fields or controls or database objects. I would
recommend renaming these.
- it would appear that you are trying to store a value in a field
which is derived by calculation/manipulation of other data. As a matter
of principle, this is seldom a good idea. Better to keep the elements
of the data "atomic", and calculate/concatenate as required, in a query
or in calculated controls, whenever needed for your purposes on form or
report.

--
Steve Schapel, Microsoft Access MVP
This is what I have in a table (MyData) and userform has the same objects
TrackNo as text
DateOpen as date (ShortDate)
Cust as text
Reason as Memo

This is the query I have in place (Temp01)
Year: Year([DateOpen])
Month: Month([DateOpen])
HowMany: Count([Year]+[Month])
This is the SQL for HowMany field in the query
SELECT Year([DateOpen]) AS [Year], Month([DateOpen]) AS [Month],
Count([Year]+[Month]) AS HowMany
FROM MyData
GROUP BY Year([DateOpen]), Month([DateOpen]);

This is want I want to do on user form:
Person enters the DateOpen in textbox and this runs the AfterUpdate event.
The event returns HowMany value from the query and I can complete the event
by adding 1 to that value and set the TrackingNo as yymmdd-HowMany,â€000â€

What can you offer to help me with this? I have used Dcount until my head
hurts to do this without the query and still cannot seem to solve this
problem so am now trying to get help with the query method.

Thank you for your assistance.
Click to expand...
Click to expand...
 
George,

Ok, thanks for the further information, I think I now understand what
you are doing.

The DCount expression you were probably looking for, depending where and
how you would be using it, would be something like this...
DCount("*","CcnData","Format([DateOpen],"yyyymm")='" &
Format([DateOpen],"yyyymm") & "'")

So, you could use code something along these lines, on the After Update
event of the DateOpen textbox...

Me.TrackNo = "C" & Format([DateOpen],"yymm") & "-" &
Format(DCount("*","CcnData","Format([DateOpen],'yyyymm')='" &
Format([DateOpen],"yyyymm") & "'")+1,"00")

As indicated in my earlier reply, I consider this approach unwise. If
it was mine, I think I would use a number data type field to keep the
serial number. There is no reason to have this concatenated TrackNo in
the table. When you need it in this format on a form or report, it is
better to construct it at that time.

There is another question that needs to be considered... what happens if
a record is deleted? Or a data entry error is made and the date needs
to be amended? There can be problems with basing the serialising of the
numbers on the Count. More common is to use the Max and add 1... but
that of course leaves the gaps in the numbering in the case of a
deletion or amendment. So, just highlighting some considerations... a
fuller discussion would depend on how you really want it to work.

--
Steve Schapel, Microsoft Access MVP
Dear Steve,

Here are the details on what I am trying to do with this application.

The query does work as listed below.
This returns the count for the year+month from the date value in the table.
These are the fileds in the query that returns how many entries in the table
based on DateOpen field

Year: Year([DateOpen])
Month: Month([DateOpen])
HowMany: Count([Year]+[Month])

Query Data Results
Year Month HowMany
2005 7 1
2006 7 8
2006 8 3
2006 9 1
2006 10 2

This is the SQL statement created when I make this query.
SELECT Year([DateOpen]) AS [Year], Month([DateOpen]) AS [Month],
Count([Year]+[Month]) AS HowMany
FROM CcnData
GROUP BY Year([DateOpen]), Month([DateOpen]);

This is the data table showing how I want to use this information to create
my TrackNo to be able to see the progress.

If for example the user entered a new entry with the date 7/30/2006 the
AfterUpdate attached to the textbox would return 9 based on there are 8
counts for the year 2006 and month 7 as seen in the query. I then can use
this to create a TrackNo as C0607-09 (Cyymm-TrackNo). I am not sure which is
the best method to use to get this number. Have the query that works but not
sure how to get this value when the DateOpen (textbox) AfterUpdate event
fires on user form. I have used DCount till my head hurts and cannot seem to
figure this out either.

MyData
TrackNo DateOpen Reason
C0507-01 7/5/2005 Test
C0607-01 7/5/2006 Test
C0607-02 7/12/2006 Any
C0607-03 7/13/2006 Test
C0607-04 7/15/2006 Test
C0607-05 7/15/2006
C0607-06 7/25/2006 TEST
C0607-07 7/25/2006
C0607-08 7/29/2006 ANY
C0608-01 8/6/2006
C0608-02 8/15/2006 ANY
C0608-03 8/15/2006
C0609-01 9/15/2006
C0610-01 10/1/2006
C0610-02 10/2/2006

If you need additional information please let me know.

Thank you for your assistance
Click to expand...
 
Steve,

I agree that trying to do this can caouse problems with delections of
records. Could be that i am just so stuck in Excel is confusing me and should
just use the autonumber for this function. Because the query is working I can
see how many entires which satisfies this purpose therefore only need this
for a report.

My use in Excel made this unique number for me but did cause problems when
records were deleted that corrupt the indexing. Still learning Access and
want to get the most out of this in order to convert from the old.

Thanks for your help

--
George G


Steve Schapel said:
George,

Ok, thanks for the further information, I think I now understand what
you are doing.

The DCount expression you were probably looking for, depending where and
how you would be using it, would be something like this...
DCount("*","CcnData","Format([DateOpen],"yyyymm")='" &
Format([DateOpen],"yyyymm") & "'")

So, you could use code something along these lines, on the After Update
event of the DateOpen textbox...

Me.TrackNo = "C" & Format([DateOpen],"yymm") & "-" &
Format(DCount("*","CcnData","Format([DateOpen],'yyyymm')='" &
Format([DateOpen],"yyyymm") & "'")+1,"00")

As indicated in my earlier reply, I consider this approach unwise. If
it was mine, I think I would use a number data type field to keep the
serial number. There is no reason to have this concatenated TrackNo in
the table. When you need it in this format on a form or report, it is
better to construct it at that time.

There is another question that needs to be considered... what happens if
a record is deleted? Or a data entry error is made and the date needs
to be amended? There can be problems with basing the serialising of the
numbers on the Count. More common is to use the Max and add 1... but
that of course leaves the gaps in the numbering in the case of a
deletion or amendment. So, just highlighting some considerations... a
fuller discussion would depend on how you really want it to work.

--
Steve Schapel, Microsoft Access MVP
Dear Steve,

Here are the details on what I am trying to do with this application.

The query does work as listed below.
This returns the count for the year+month from the date value in the table.
These are the fileds in the query that returns how many entries in the table
based on DateOpen field

Year: Year([DateOpen])
Month: Month([DateOpen])
HowMany: Count([Year]+[Month])

Query Data Results
Year Month HowMany
2005 7 1
2006 7 8
2006 8 3
2006 9 1
2006 10 2

This is the SQL statement created when I make this query.
SELECT Year([DateOpen]) AS [Year], Month([DateOpen]) AS [Month],
Count([Year]+[Month]) AS HowMany
FROM CcnData
GROUP BY Year([DateOpen]), Month([DateOpen]);

This is the data table showing how I want to use this information to create
my TrackNo to be able to see the progress.

If for example the user entered a new entry with the date 7/30/2006 the
AfterUpdate attached to the textbox would return 9 based on there are 8
counts for the year 2006 and month 7 as seen in the query. I then can use
this to create a TrackNo as C0607-09 (Cyymm-TrackNo). I am not sure which is
the best method to use to get this number. Have the query that works but not
sure how to get this value when the DateOpen (textbox) AfterUpdate event
fires on user form. I have used DCount till my head hurts and cannot seem to
figure this out either.

MyData
TrackNo DateOpen Reason
C0507-01 7/5/2005 Test
C0607-01 7/5/2006 Test
C0607-02 7/12/2006 Any
C0607-03 7/13/2006 Test
C0607-04 7/15/2006 Test
C0607-05 7/15/2006
C0607-06 7/25/2006 TEST
C0607-07 7/25/2006
C0607-08 7/29/2006 ANY
C0608-01 8/6/2006
C0608-02 8/15/2006 ANY
C0608-03 8/15/2006
C0609-01 9/15/2006
C0610-01 10/1/2006
C0610-02 10/2/2006

If you need additional information please let me know.

Thank you for your assistance
Click to expand...
Click to expand...
 
Back
Top