If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Customer Count Database
Since VisitSA is a string and I assume SA is a text field, your syntax is
incorrect. You have it coded is if it were a date field on the left side, but as text on the right side. This should fix it. IN Jet SQL, the delimters a For Numbers - No Delimiters For Text - Single or Double Quotes. For Dates - # strSQL = "INSERT INTO tblDeptVisits (Dept, SA, VisitTime ) " & _ "SELECT """ & VisitDept & """ AS Expr1, """ & VisitSA & """ AS Expr2, #" & Now() & "# AS Expr3;" -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: I'm trying to expand the answer in the module to also pick up the overall depaertment. I changed the code to (below) where VisitSA was the additional code. I updated my buttons oclick code to Call AddVisitor("Building Control", "PER") Public Sub AddVisitor(VisitDept, VisitSA As String) Dim strSQL As String On Error GoTo AddVisitor_Error strSQL = "INSERT INTO tblDeptVisits (Dept, SA, VisitTime ) " & _ "SELECT """ & VisitDept & """ AS Expr1, #" & VisitSA & """ AS Expr2, #" & Now() & "# AS Expr3;" CurrentDb.Execute strSQL, dbFailOnError ....plus all the rest I now get this error message when i click a button to record the details. error 3075 (syntax error (missing operator) in query expression '#PER" AS Expr2, #04/06/2008 11:53:57# AS Expr3;'.) in procedure AddVisitor of VBA Form_Form4 Would you mind taking a look and letting me know what adjustment I need to make. I have managed to design a report, so thats all good ------------------------------------------------------------------------------------- "Klatuu" wrote: Glad I could help. Now, you will probably want to be able to report on this. To get the reporting you need, you will want to use Sorting and Grouping in your report to get the counts by time period. Post back if you need help with that. -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: I did it, I changed the VisitCity in the Module code to VisitDept and it works. Ace, you did it, thank you so much, you said it was simple..it was. Amazing. "Mr-Re Man" wrote: We're just about there, no errors popping up, the only detail is the department (EE or PER) not populating the table. I can taste success and a pat on the back for us! "Klatuu" wrote: The function I sent is defined as a Private sub because I had it in the form I built for testing. Since you have it in a separate module, change Private to Public: Public Sub AddVisitor(VisitDept As String) Expr1 and Expr2 do not have to be changed. They are actually just place holders to satisfy SQL syntax requirements. -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: I think I'm getting there, I have a table called tblDeptVisits with 3 fields ("ID" as an autonumber, "Dept" as Text and "VisitTime" as Date/Time) My form is called Form1 with two buttons, 1 called cmdEE the other cmdPER, both have an onClick event that states: Private Sub cmdEE_Click() Call AddVisitor("EE") and one for PER End Sub My Module is called MODcount and it has the information you provided me, not sure if I have to change the text VisitCity, Expr1 and Expr2 in the code to reflect something in my database. However, I left it as it is and opened the form and clicked a button, to which I received the followign error. It opened the coding screen and indicated a compile error, sub or function not defined and within the code (below) Cal AddVisitor was shaded in grey. Private Sub cmdEE_Click() Call AddVisitor("EE") End Sub I guess I have left off something crucial. thanks again for your time "Klatuu" wrote: -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: Thank you for the fast response, its was as fast as your solution. Excuse my typing in capitals, it is only to ask a question based on your response. This will be very simple and very fast for the reception people to use. Create a form that has a command button for each department. In the form module, put a sub like this: WHAT IS A FORM MODULE, IS THIS A LINK IN THE FORM WITH THE BUTTONS I JUST CREATED OR SOMEWHERE ELSE? A form module is the module that is attached to the form. When you click on the Code Builder option in the event procedure, it opens the form's module in the VB Edtior. Nothing magic about it. So, the answer is yes. Private Sub AddVisitor(VisitDept As String) Dim strSQL As String On Error GoTo AddVisitor_Error strSQL = "INSERT INTO tblDeptVisits ( Dept, VisitTime ) " & _ "SELECT """ & VisitCity & """ AS Expr1, #" & Now() & "# AS Expr2;" CurrentDb.Execute strSQL, dbFailOnError AddVisitor_Exit: On Error GoTo 0 Exit Sub AddVisitor_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & _ ") in procedure AddVisitor of VBA Document Form_Form4" GoTo AddVisitor_Exit End Sub Now, for each command button, you will call the sub with the name of the department like this: Private Sub Command0_Click() Call AddVisitor("PeanutButter") End Sub I HAVE PUT THE ABOVE CODE IN MODULES AND CALLED IT MODcount, IS THAT CORRECT? That will do. You could just paste it into the form's code. You would put it below the Option statments but before any event procedures. So all the user has to do is click the button once for each vistor to the department. It will add a record to the table. HOW? That is what the code does in the sub I provided. It builds an SQL statment to create a new record in your table with the name of the department and the current date and time. Then the CurrentDb.Execute statment executes the SQL and adds the record to the table. The table will need two fields. A text field for the department name and a Date/Time field for the visit. DOES THE TABLE LITERALLY HAVE TWO FIELDS SET-UP? ANY KEY FIELD? That is up to you. I would suggest you add a primary key field. I would use an autonumber field and designate it as the primary key field. You will have to modify the code to use your table and field names. Rather than doing the count in the table, use forms or reports to display the counts by department and time period. You will usually do this using totals queries. AS YOU CAN SEE MY SKILLS AREN'T NOWHERE AS NEAR AS GOOD AS YOURS FOR EVEN A SIMPLE DATABASE, YOUR HELP IS MUCH APPRECIATED. Glad to help. Post back if you need additional assistance. |
#12
|
|||
|
|||
Customer Count Database
Once again, thank you for the help - I think we have this one nailed now!
"Klatuu" wrote: Since VisitSA is a string and I assume SA is a text field, your syntax is incorrect. You have it coded is if it were a date field on the left side, but as text on the right side. This should fix it. IN Jet SQL, the delimters a For Numbers - No Delimiters For Text - Single or Double Quotes. For Dates - # strSQL = "INSERT INTO tblDeptVisits (Dept, SA, VisitTime ) " & _ "SELECT """ & VisitDept & """ AS Expr1, """ & VisitSA & """ AS Expr2, #" & Now() & "# AS Expr3;" -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: I'm trying to expand the answer in the module to also pick up the overall depaertment. I changed the code to (below) where VisitSA was the additional code. I updated my buttons oclick code to Call AddVisitor("Building Control", "PER") Public Sub AddVisitor(VisitDept, VisitSA As String) Dim strSQL As String On Error GoTo AddVisitor_Error strSQL = "INSERT INTO tblDeptVisits (Dept, SA, VisitTime ) " & _ "SELECT """ & VisitDept & """ AS Expr1, #" & VisitSA & """ AS Expr2, #" & Now() & "# AS Expr3;" CurrentDb.Execute strSQL, dbFailOnError ....plus all the rest I now get this error message when i click a button to record the details. error 3075 (syntax error (missing operator) in query expression '#PER" AS Expr2, #04/06/2008 11:53:57# AS Expr3;'.) in procedure AddVisitor of VBA Form_Form4 Would you mind taking a look and letting me know what adjustment I need to make. I have managed to design a report, so thats all good ------------------------------------------------------------------------------------- "Klatuu" wrote: Glad I could help. Now, you will probably want to be able to report on this. To get the reporting you need, you will want to use Sorting and Grouping in your report to get the counts by time period. Post back if you need help with that. -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: I did it, I changed the VisitCity in the Module code to VisitDept and it works. Ace, you did it, thank you so much, you said it was simple..it was. Amazing. "Mr-Re Man" wrote: We're just about there, no errors popping up, the only detail is the department (EE or PER) not populating the table. I can taste success and a pat on the back for us! "Klatuu" wrote: The function I sent is defined as a Private sub because I had it in the form I built for testing. Since you have it in a separate module, change Private to Public: Public Sub AddVisitor(VisitDept As String) Expr1 and Expr2 do not have to be changed. They are actually just place holders to satisfy SQL syntax requirements. -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: I think I'm getting there, I have a table called tblDeptVisits with 3 fields ("ID" as an autonumber, "Dept" as Text and "VisitTime" as Date/Time) My form is called Form1 with two buttons, 1 called cmdEE the other cmdPER, both have an onClick event that states: Private Sub cmdEE_Click() Call AddVisitor("EE") and one for PER End Sub My Module is called MODcount and it has the information you provided me, not sure if I have to change the text VisitCity, Expr1 and Expr2 in the code to reflect something in my database. However, I left it as it is and opened the form and clicked a button, to which I received the followign error. It opened the coding screen and indicated a compile error, sub or function not defined and within the code (below) Cal AddVisitor was shaded in grey. Private Sub cmdEE_Click() Call AddVisitor("EE") End Sub I guess I have left off something crucial. thanks again for your time "Klatuu" wrote: -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: Thank you for the fast response, its was as fast as your solution. Excuse my typing in capitals, it is only to ask a question based on your response. This will be very simple and very fast for the reception people to use. Create a form that has a command button for each department. In the form module, put a sub like this: WHAT IS A FORM MODULE, IS THIS A LINK IN THE FORM WITH THE BUTTONS I JUST CREATED OR SOMEWHERE ELSE? A form module is the module that is attached to the form. When you click on the Code Builder option in the event procedure, it opens the form's module in the VB Edtior. Nothing magic about it. So, the answer is yes. Private Sub AddVisitor(VisitDept As String) Dim strSQL As String On Error GoTo AddVisitor_Error strSQL = "INSERT INTO tblDeptVisits ( Dept, VisitTime ) " & _ "SELECT """ & VisitCity & """ AS Expr1, #" & Now() & "# AS Expr2;" CurrentDb.Execute strSQL, dbFailOnError AddVisitor_Exit: On Error GoTo 0 Exit Sub AddVisitor_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & _ ") in procedure AddVisitor of VBA Document Form_Form4" GoTo AddVisitor_Exit End Sub Now, for each command button, you will call the sub with the name of the department like this: Private Sub Command0_Click() Call AddVisitor("PeanutButter") End Sub I HAVE PUT THE ABOVE CODE IN MODULES AND CALLED IT MODcount, IS THAT CORRECT? That will do. You could just paste it into the form's code. You would put it below the Option statments but before any event procedures. So all the user has to do is click the button once for each vistor to the department. It will add a record to the table. HOW? That is what the code does in the sub I provided. It builds an SQL statment to create a new record in your table with the name of the department and the current date and time. Then the CurrentDb.Execute statment executes the SQL and adds the record to the table. The table will need two fields. A text field for the department name and a Date/Time field for the visit. DOES THE TABLE LITERALLY HAVE TWO FIELDS SET-UP? ANY KEY FIELD? That is up to you. I would suggest you add a primary key field. I would use an autonumber field and designate it as the primary key field. You will have to modify the code to use your table and field names. Rather than doing the count in the table, use forms or reports to display the counts by department and time period. You will usually do this using totals queries. AS YOU CAN SEE MY SKILLS AREN'T NOWHERE AS NEAR AS GOOD AS YOURS FOR EVEN A SIMPLE DATABASE, YOUR HELP IS MUCH APPRECIATED. Glad to help. Post back if you need additional assistance. |
#13
|
|||
|
|||
Customer Count Database
One final thing I promise, I only just noticed that the date from the module
is updating the table in US format, how do I change this to UK? I did try making the date field in the tables Format Properties to dd/mm/yyyy. But nothing altered. "Klatuu" wrote: Since VisitSA is a string and I assume SA is a text field, your syntax is incorrect. You have it coded is if it were a date field on the left side, but as text on the right side. This should fix it. IN Jet SQL, the delimters a For Numbers - No Delimiters For Text - Single or Double Quotes. For Dates - # strSQL = "INSERT INTO tblDeptVisits (Dept, SA, VisitTime ) " & _ "SELECT """ & VisitDept & """ AS Expr1, """ & VisitSA & """ AS Expr2, #" & Now() & "# AS Expr3;" -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: I'm trying to expand the answer in the module to also pick up the overall depaertment. I changed the code to (below) where VisitSA was the additional code. I updated my buttons oclick code to Call AddVisitor("Building Control", "PER") Public Sub AddVisitor(VisitDept, VisitSA As String) Dim strSQL As String On Error GoTo AddVisitor_Error strSQL = "INSERT INTO tblDeptVisits (Dept, SA, VisitTime ) " & _ "SELECT """ & VisitDept & """ AS Expr1, #" & VisitSA & """ AS Expr2, #" & Now() & "# AS Expr3;" CurrentDb.Execute strSQL, dbFailOnError ....plus all the rest I now get this error message when i click a button to record the details. error 3075 (syntax error (missing operator) in query expression '#PER" AS Expr2, #04/06/2008 11:53:57# AS Expr3;'.) in procedure AddVisitor of VBA Form_Form4 Would you mind taking a look and letting me know what adjustment I need to make. I have managed to design a report, so thats all good ------------------------------------------------------------------------------------- "Klatuu" wrote: Glad I could help. Now, you will probably want to be able to report on this. To get the reporting you need, you will want to use Sorting and Grouping in your report to get the counts by time period. Post back if you need help with that. -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: I did it, I changed the VisitCity in the Module code to VisitDept and it works. Ace, you did it, thank you so much, you said it was simple..it was. Amazing. "Mr-Re Man" wrote: We're just about there, no errors popping up, the only detail is the department (EE or PER) not populating the table. I can taste success and a pat on the back for us! "Klatuu" wrote: The function I sent is defined as a Private sub because I had it in the form I built for testing. Since you have it in a separate module, change Private to Public: Public Sub AddVisitor(VisitDept As String) Expr1 and Expr2 do not have to be changed. They are actually just place holders to satisfy SQL syntax requirements. -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: I think I'm getting there, I have a table called tblDeptVisits with 3 fields ("ID" as an autonumber, "Dept" as Text and "VisitTime" as Date/Time) My form is called Form1 with two buttons, 1 called cmdEE the other cmdPER, both have an onClick event that states: Private Sub cmdEE_Click() Call AddVisitor("EE") and one for PER End Sub My Module is called MODcount and it has the information you provided me, not sure if I have to change the text VisitCity, Expr1 and Expr2 in the code to reflect something in my database. However, I left it as it is and opened the form and clicked a button, to which I received the followign error. It opened the coding screen and indicated a compile error, sub or function not defined and within the code (below) Cal AddVisitor was shaded in grey. Private Sub cmdEE_Click() Call AddVisitor("EE") End Sub I guess I have left off something crucial. thanks again for your time "Klatuu" wrote: -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: Thank you for the fast response, its was as fast as your solution. Excuse my typing in capitals, it is only to ask a question based on your response. This will be very simple and very fast for the reception people to use. Create a form that has a command button for each department. In the form module, put a sub like this: WHAT IS A FORM MODULE, IS THIS A LINK IN THE FORM WITH THE BUTTONS I JUST CREATED OR SOMEWHERE ELSE? A form module is the module that is attached to the form. When you click on the Code Builder option in the event procedure, it opens the form's module in the VB Edtior. Nothing magic about it. So, the answer is yes. Private Sub AddVisitor(VisitDept As String) Dim strSQL As String On Error GoTo AddVisitor_Error strSQL = "INSERT INTO tblDeptVisits ( Dept, VisitTime ) " & _ "SELECT """ & VisitCity & """ AS Expr1, #" & Now() & "# AS Expr2;" CurrentDb.Execute strSQL, dbFailOnError AddVisitor_Exit: On Error GoTo 0 Exit Sub AddVisitor_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & _ ") in procedure AddVisitor of VBA Document Form_Form4" GoTo AddVisitor_Exit End Sub Now, for each command button, you will call the sub with the name of the department like this: Private Sub Command0_Click() Call AddVisitor("PeanutButter") End Sub I HAVE PUT THE ABOVE CODE IN MODULES AND CALLED IT MODcount, IS THAT CORRECT? That will do. You could just paste it into the form's code. You would put it below the Option statments but before any event procedures. So all the user has to do is click the button once for each vistor to the department. It will add a record to the table. HOW? That is what the code does in the sub I provided. It builds an SQL statment to create a new record in your table with the name of the department and the current date and time. Then the CurrentDb.Execute statment executes the SQL and adds the record to the table. The table will need two fields. A text field for the department name and a Date/Time field for the visit. DOES THE TABLE LITERALLY HAVE TWO FIELDS SET-UP? ANY KEY FIELD? That is up to you. I would suggest you add a primary key field. I would use an autonumber field and designate it as the primary key field. You will have to modify the code to use your table and field names. Rather than doing the count in the table, use forms or reports to display the counts by department and time period. You will usually do this using totals queries. AS YOU CAN SEE MY SKILLS AREN'T NOWHERE AS NEAR AS GOOD AS YOURS FOR EVEN A SIMPLE DATABASE, YOUR HELP IS MUCH APPRECIATED. Glad to help. Post back if you need additional assistance. |
#14
|
|||
|
|||
Customer Count Database
Date fields are not stored in any format. They are actuall floating point
decimal numbers where the whole number represents the date and the decimal part represents the time. How they are displayed depends first on the regional settings on your computer and then on how you format them. -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: One final thing I promise, I only just noticed that the date from the module is updating the table in US format, how do I change this to UK? I did try making the date field in the tables Format Properties to dd/mm/yyyy. But nothing altered. "Klatuu" wrote: Since VisitSA is a string and I assume SA is a text field, your syntax is incorrect. You have it coded is if it were a date field on the left side, but as text on the right side. This should fix it. IN Jet SQL, the delimters a For Numbers - No Delimiters For Text - Single or Double Quotes. For Dates - # strSQL = "INSERT INTO tblDeptVisits (Dept, SA, VisitTime ) " & _ "SELECT """ & VisitDept & """ AS Expr1, """ & VisitSA & """ AS Expr2, #" & Now() & "# AS Expr3;" -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: I'm trying to expand the answer in the module to also pick up the overall depaertment. I changed the code to (below) where VisitSA was the additional code. I updated my buttons oclick code to Call AddVisitor("Building Control", "PER") Public Sub AddVisitor(VisitDept, VisitSA As String) Dim strSQL As String On Error GoTo AddVisitor_Error strSQL = "INSERT INTO tblDeptVisits (Dept, SA, VisitTime ) " & _ "SELECT """ & VisitDept & """ AS Expr1, #" & VisitSA & """ AS Expr2, #" & Now() & "# AS Expr3;" CurrentDb.Execute strSQL, dbFailOnError ....plus all the rest I now get this error message when i click a button to record the details. error 3075 (syntax error (missing operator) in query expression '#PER" AS Expr2, #04/06/2008 11:53:57# AS Expr3;'.) in procedure AddVisitor of VBA Form_Form4 Would you mind taking a look and letting me know what adjustment I need to make. I have managed to design a report, so thats all good ------------------------------------------------------------------------------------- "Klatuu" wrote: Glad I could help. Now, you will probably want to be able to report on this. To get the reporting you need, you will want to use Sorting and Grouping in your report to get the counts by time period. Post back if you need help with that. -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: I did it, I changed the VisitCity in the Module code to VisitDept and it works. Ace, you did it, thank you so much, you said it was simple..it was. Amazing. "Mr-Re Man" wrote: We're just about there, no errors popping up, the only detail is the department (EE or PER) not populating the table. I can taste success and a pat on the back for us! "Klatuu" wrote: The function I sent is defined as a Private sub because I had it in the form I built for testing. Since you have it in a separate module, change Private to Public: Public Sub AddVisitor(VisitDept As String) Expr1 and Expr2 do not have to be changed. They are actually just place holders to satisfy SQL syntax requirements. -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: I think I'm getting there, I have a table called tblDeptVisits with 3 fields ("ID" as an autonumber, "Dept" as Text and "VisitTime" as Date/Time) My form is called Form1 with two buttons, 1 called cmdEE the other cmdPER, both have an onClick event that states: Private Sub cmdEE_Click() Call AddVisitor("EE") and one for PER End Sub My Module is called MODcount and it has the information you provided me, not sure if I have to change the text VisitCity, Expr1 and Expr2 in the code to reflect something in my database. However, I left it as it is and opened the form and clicked a button, to which I received the followign error. It opened the coding screen and indicated a compile error, sub or function not defined and within the code (below) Cal AddVisitor was shaded in grey. Private Sub cmdEE_Click() Call AddVisitor("EE") End Sub I guess I have left off something crucial. thanks again for your time "Klatuu" wrote: -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: Thank you for the fast response, its was as fast as your solution. Excuse my typing in capitals, it is only to ask a question based on your response. This will be very simple and very fast for the reception people to use. Create a form that has a command button for each department. In the form module, put a sub like this: WHAT IS A FORM MODULE, IS THIS A LINK IN THE FORM WITH THE BUTTONS I JUST CREATED OR SOMEWHERE ELSE? A form module is the module that is attached to the form. When you click on the Code Builder option in the event procedure, it opens the form's module in the VB Edtior. Nothing magic about it. So, the answer is yes. Private Sub AddVisitor(VisitDept As String) Dim strSQL As String On Error GoTo AddVisitor_Error strSQL = "INSERT INTO tblDeptVisits ( Dept, VisitTime ) " & _ "SELECT """ & VisitCity & """ AS Expr1, #" & Now() & "# AS Expr2;" CurrentDb.Execute strSQL, dbFailOnError AddVisitor_Exit: On Error GoTo 0 Exit Sub AddVisitor_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & _ ") in procedure AddVisitor of VBA Document Form_Form4" GoTo AddVisitor_Exit End Sub Now, for each command button, you will call the sub with the name of the department like this: Private Sub Command0_Click() Call AddVisitor("PeanutButter") End Sub I HAVE PUT THE ABOVE CODE IN MODULES AND CALLED IT MODcount, IS THAT CORRECT? That will do. You could just paste it into the form's code. You would put it below the Option statments but before any event procedures. So all the user has to do is click the button once for each vistor to the department. It will add a record to the table. HOW? That is what the code does in the sub I provided. It builds an SQL statment to create a new record in your table with the name of the department and the current date and time. Then the CurrentDb.Execute statment executes the SQL and adds the record to the table. The table will need two fields. A text field for the department name and a Date/Time field for the visit. DOES THE TABLE LITERALLY HAVE TWO FIELDS SET-UP? ANY KEY FIELD? That is up to you. I would suggest you add a primary key field. I would use an autonumber field and designate it as the primary key field. You will have to modify the code to use your table and field names. Rather than doing the count in the table, use forms or reports to display the counts by department and time period. You will usually do this using totals queries. AS YOU CAN SEE MY SKILLS AREN'T NOWHERE AS NEAR AS GOOD AS YOURS FOR EVEN A SIMPLE DATABASE, YOUR HELP IS MUCH APPRECIATED. Glad to help. Post back if you need additional assistance. |
#15
|
|||
|
|||
Customer Count Database
I've been trying a few different options to try to resolve the date issue.
So today, in the table I changed the date field to be a numeric field so it gives me a number. I changed todays date on my pc as the 6/6/08 wouldn't really tell me anything, so when I clicked on a button and it populated the table, the numeric string I got was 39635. I copied this into excel and changed it into a date and it came up as 6 July 2008. All my other dates on the pc seem to be working fine, would you have any further ideas as to what i can do as it will make the reports meaningless when viewing stats between two different dates. This is a strangeone, that has me stumped. "Klatuu" wrote: Date fields are not stored in any format. They are actuall floating point decimal numbers where the whole number represents the date and the decimal part represents the time. How they are displayed depends first on the regional settings on your computer and then on how you format them. -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: One final thing I promise, I only just noticed that the date from the module is updating the table in US format, how do I change this to UK? I did try making the date field in the tables Format Properties to dd/mm/yyyy. But nothing altered. "Klatuu" wrote: Since VisitSA is a string and I assume SA is a text field, your syntax is incorrect. You have it coded is if it were a date field on the left side, but as text on the right side. This should fix it. IN Jet SQL, the delimters a For Numbers - No Delimiters For Text - Single or Double Quotes. For Dates - # strSQL = "INSERT INTO tblDeptVisits (Dept, SA, VisitTime ) " & _ "SELECT """ & VisitDept & """ AS Expr1, """ & VisitSA & """ AS Expr2, #" & Now() & "# AS Expr3;" -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: I'm trying to expand the answer in the module to also pick up the overall depaertment. I changed the code to (below) where VisitSA was the additional code. I updated my buttons oclick code to Call AddVisitor("Building Control", "PER") Public Sub AddVisitor(VisitDept, VisitSA As String) Dim strSQL As String On Error GoTo AddVisitor_Error strSQL = "INSERT INTO tblDeptVisits (Dept, SA, VisitTime ) " & _ "SELECT """ & VisitDept & """ AS Expr1, #" & VisitSA & """ AS Expr2, #" & Now() & "# AS Expr3;" CurrentDb.Execute strSQL, dbFailOnError ....plus all the rest I now get this error message when i click a button to record the details. error 3075 (syntax error (missing operator) in query expression '#PER" AS Expr2, #04/06/2008 11:53:57# AS Expr3;'.) in procedure AddVisitor of VBA Form_Form4 Would you mind taking a look and letting me know what adjustment I need to make. I have managed to design a report, so thats all good ------------------------------------------------------------------------------------- "Klatuu" wrote: Glad I could help. Now, you will probably want to be able to report on this. To get the reporting you need, you will want to use Sorting and Grouping in your report to get the counts by time period. Post back if you need help with that. -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: I did it, I changed the VisitCity in the Module code to VisitDept and it works. Ace, you did it, thank you so much, you said it was simple..it was. Amazing. "Mr-Re Man" wrote: We're just about there, no errors popping up, the only detail is the department (EE or PER) not populating the table. I can taste success and a pat on the back for us! "Klatuu" wrote: The function I sent is defined as a Private sub because I had it in the form I built for testing. Since you have it in a separate module, change Private to Public: Public Sub AddVisitor(VisitDept As String) Expr1 and Expr2 do not have to be changed. They are actually just place holders to satisfy SQL syntax requirements. -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: I think I'm getting there, I have a table called tblDeptVisits with 3 fields ("ID" as an autonumber, "Dept" as Text and "VisitTime" as Date/Time) My form is called Form1 with two buttons, 1 called cmdEE the other cmdPER, both have an onClick event that states: Private Sub cmdEE_Click() Call AddVisitor("EE") and one for PER End Sub My Module is called MODcount and it has the information you provided me, not sure if I have to change the text VisitCity, Expr1 and Expr2 in the code to reflect something in my database. However, I left it as it is and opened the form and clicked a button, to which I received the followign error. It opened the coding screen and indicated a compile error, sub or function not defined and within the code (below) Cal AddVisitor was shaded in grey. Private Sub cmdEE_Click() Call AddVisitor("EE") End Sub I guess I have left off something crucial. thanks again for your time "Klatuu" wrote: -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: Thank you for the fast response, its was as fast as your solution. Excuse my typing in capitals, it is only to ask a question based on your response. This will be very simple and very fast for the reception people to use. Create a form that has a command button for each department. In the form module, put a sub like this: WHAT IS A FORM MODULE, IS THIS A LINK IN THE FORM WITH THE BUTTONS I JUST CREATED OR SOMEWHERE ELSE? A form module is the module that is attached to the form. When you click on the Code Builder option in the event procedure, it opens the form's module in the VB Edtior. Nothing magic about it. So, the answer is yes. Private Sub AddVisitor(VisitDept As String) Dim strSQL As String On Error GoTo AddVisitor_Error strSQL = "INSERT INTO tblDeptVisits ( Dept, VisitTime ) " & _ "SELECT """ & VisitCity & """ AS Expr1, #" & Now() & "# AS Expr2;" CurrentDb.Execute strSQL, dbFailOnError AddVisitor_Exit: On Error GoTo 0 Exit Sub AddVisitor_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & _ ") in procedure AddVisitor of VBA Document Form_Form4" GoTo AddVisitor_Exit End Sub Now, for each command button, you will call the sub with the name of the department like this: Private Sub Command0_Click() Call AddVisitor("PeanutButter") End Sub I HAVE PUT THE ABOVE CODE IN MODULES AND CALLED IT MODcount, IS THAT CORRECT? That will do. You could just paste it into the form's code. You would put it below the Option statments but before any event procedures. So all the user has to do is click the button once for each vistor to the department. It will add a record to the table. HOW? That is what the code does in the sub I provided. It builds an SQL statment to create a new record in your table with the name of the department and the current date and time. Then the CurrentDb.Execute statment executes the SQL and adds the record to the table. The table will need two fields. A text field for the department name and a Date/Time field for the visit. DOES THE TABLE LITERALLY HAVE TWO FIELDS SET-UP? ANY KEY FIELD? That is up to you. I would suggest you add a primary key field. I would use an autonumber field and designate it as the primary key field. You will have to modify the code to use your table and field names. Rather than doing the count in the table, use forms or reports to display the counts by department and time period. You will usually do this using totals queries. AS YOU CAN SEE MY SKILLS AREN'T NOWHERE AS NEAR AS GOOD AS YOURS FOR EVEN A SIMPLE DATABASE, YOUR HELP IS MUCH APPRECIATED. Glad to help. Post back if you need additional assistance. |
#16
|
|||
|
|||
Customer Count Database
It does seem strange, but without it in front of me to experiment with, I
don't know what I can do to help. If it is just the one date, I would suspect there is something different about the control where you are displaying it or something in the field definition that is causing the problem. -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: I've been trying a few different options to try to resolve the date issue. So today, in the table I changed the date field to be a numeric field so it gives me a number. I changed todays date on my pc as the 6/6/08 wouldn't really tell me anything, so when I clicked on a button and it populated the table, the numeric string I got was 39635. I copied this into excel and changed it into a date and it came up as 6 July 2008. All my other dates on the pc seem to be working fine, would you have any further ideas as to what i can do as it will make the reports meaningless when viewing stats between two different dates. This is a strangeone, that has me stumped. "Klatuu" wrote: Date fields are not stored in any format. They are actuall floating point decimal numbers where the whole number represents the date and the decimal part represents the time. How they are displayed depends first on the regional settings on your computer and then on how you format them. -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: One final thing I promise, I only just noticed that the date from the module is updating the table in US format, how do I change this to UK? I did try making the date field in the tables Format Properties to dd/mm/yyyy. But nothing altered. "Klatuu" wrote: Since VisitSA is a string and I assume SA is a text field, your syntax is incorrect. You have it coded is if it were a date field on the left side, but as text on the right side. This should fix it. IN Jet SQL, the delimters a For Numbers - No Delimiters For Text - Single or Double Quotes. For Dates - # strSQL = "INSERT INTO tblDeptVisits (Dept, SA, VisitTime ) " & _ "SELECT """ & VisitDept & """ AS Expr1, """ & VisitSA & """ AS Expr2, #" & Now() & "# AS Expr3;" -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: I'm trying to expand the answer in the module to also pick up the overall depaertment. I changed the code to (below) where VisitSA was the additional code. I updated my buttons oclick code to Call AddVisitor("Building Control", "PER") Public Sub AddVisitor(VisitDept, VisitSA As String) Dim strSQL As String On Error GoTo AddVisitor_Error strSQL = "INSERT INTO tblDeptVisits (Dept, SA, VisitTime ) " & _ "SELECT """ & VisitDept & """ AS Expr1, #" & VisitSA & """ AS Expr2, #" & Now() & "# AS Expr3;" CurrentDb.Execute strSQL, dbFailOnError ....plus all the rest I now get this error message when i click a button to record the details. error 3075 (syntax error (missing operator) in query expression '#PER" AS Expr2, #04/06/2008 11:53:57# AS Expr3;'.) in procedure AddVisitor of VBA Form_Form4 Would you mind taking a look and letting me know what adjustment I need to make. I have managed to design a report, so thats all good ------------------------------------------------------------------------------------- "Klatuu" wrote: Glad I could help. Now, you will probably want to be able to report on this. To get the reporting you need, you will want to use Sorting and Grouping in your report to get the counts by time period. Post back if you need help with that. -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: I did it, I changed the VisitCity in the Module code to VisitDept and it works. Ace, you did it, thank you so much, you said it was simple..it was. Amazing. "Mr-Re Man" wrote: We're just about there, no errors popping up, the only detail is the department (EE or PER) not populating the table. I can taste success and a pat on the back for us! "Klatuu" wrote: The function I sent is defined as a Private sub because I had it in the form I built for testing. Since you have it in a separate module, change Private to Public: Public Sub AddVisitor(VisitDept As String) Expr1 and Expr2 do not have to be changed. They are actually just place holders to satisfy SQL syntax requirements. -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: I think I'm getting there, I have a table called tblDeptVisits with 3 fields ("ID" as an autonumber, "Dept" as Text and "VisitTime" as Date/Time) My form is called Form1 with two buttons, 1 called cmdEE the other cmdPER, both have an onClick event that states: Private Sub cmdEE_Click() Call AddVisitor("EE") and one for PER End Sub My Module is called MODcount and it has the information you provided me, not sure if I have to change the text VisitCity, Expr1 and Expr2 in the code to reflect something in my database. However, I left it as it is and opened the form and clicked a button, to which I received the followign error. It opened the coding screen and indicated a compile error, sub or function not defined and within the code (below) Cal AddVisitor was shaded in grey. Private Sub cmdEE_Click() Call AddVisitor("EE") End Sub I guess I have left off something crucial. thanks again for your time "Klatuu" wrote: -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: Thank you for the fast response, its was as fast as your solution. Excuse my typing in capitals, it is only to ask a question based on your response. This will be very simple and very fast for the reception people to use. Create a form that has a command button for each department. In the form module, put a sub like this: WHAT IS A FORM MODULE, IS THIS A LINK IN THE FORM WITH THE BUTTONS I JUST CREATED OR SOMEWHERE ELSE? A form module is the module that is attached to the form. When you click on the Code Builder option in the event procedure, it opens the form's module in the VB Edtior. Nothing magic about it. So, the answer is yes. Private Sub AddVisitor(VisitDept As String) Dim strSQL As String On Error GoTo AddVisitor_Error strSQL = "INSERT INTO tblDeptVisits ( Dept, VisitTime ) " & _ "SELECT """ & VisitCity & """ AS Expr1, #" & Now() & "# AS Expr2;" CurrentDb.Execute strSQL, dbFailOnError AddVisitor_Exit: On Error GoTo 0 Exit Sub AddVisitor_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & _ ") in procedure AddVisitor of VBA Document Form_Form4" GoTo AddVisitor_Exit End Sub Now, for each command button, you will call the sub with the name of the department like this: Private Sub Command0_Click() Call AddVisitor("PeanutButter") End Sub I HAVE PUT THE ABOVE CODE IN MODULES AND CALLED IT MODcount, IS THAT CORRECT? That will do. You could just paste it into the form's code. You would put it below the Option statments but before any event procedures. So all the user has to do is click the button once for each vistor to the department. It will add a record to the table. HOW? That is what the code does in the sub I provided. It builds an SQL statment to create a new record in your table with the name of the department and the current date and time. Then the CurrentDb.Execute statment executes the SQL and adds the record to the table. The table will need two fields. A text field for the department name and a Date/Time field for the visit. DOES THE TABLE LITERALLY HAVE TWO FIELDS SET-UP? ANY KEY FIELD? That is up to you. I would suggest you add a primary key field. I would use an autonumber field and designate it as the primary key field. You will have to modify the code to use your table and field names. Rather than doing the count in the table, use forms or reports to display the counts by department and time period. You will usually do this using totals queries. AS YOU CAN SEE MY SKILLS AREN'T NOWHERE AS NEAR AS GOOD AS YOURS FOR EVEN A SIMPLE DATABASE, YOUR HELP IS MUCH APPRECIATED. Glad to help. Post back if you need additional assistance. |
#17
|
|||
|
|||
Customer Count Database
Do you think this code would help,
Function SQLDate(vDate As Variant) As String If IsDate(vDate) Then SQLDate = "#" & Format$(vDate, "mm\/dd\/yyyy") & "#" End If End Function It was taken from a website that indicates that dates in SQL seem to stick to American formats and this code above should allow for other countries, namely UK to capture the correct format. I have tried to call it from the module you gave me, but wasn't sure how or if it would work. If I was in America this thread would have finished a long time ago |
#18
|
|||
|
|||
Customer Count Database
That format is for a US date, don't you want "dd\/mm\/yyyy"?
Where do you want to present the results? -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: Do you think this code would help, Function SQLDate(vDate As Variant) As String If IsDate(vDate) Then SQLDate = "#" & Format$(vDate, "mm\/dd\/yyyy") & "#" End If End Function It was taken from a website that indicates that dates in SQL seem to stick to American formats and this code above should allow for other countries, namely UK to capture the correct format. I have tried to call it from the module you gave me, but wasn't sure how or if it would work. If I was in America this thread would have finished a long time ago |
#19
|
|||
|
|||
Customer Count Database
The module you gave me here (part of)..
Subject: Customer Count Database 6/2/2008 9:53 AM PST By: Klatuu In: microsoft.public.access.tablesdbdesign -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: Thank you for the fast response, its was as fast as your solution. Excuse my typing in capitals, it is only to ask a question based on your response. This will be very simple and very fast for the reception people to use. Create a form that has a command button for each department. In the form module, put a sub like this: WHAT IS A FORM MODULE, IS THIS A LINK IN THE FORM WITH THE BUTTONS I JUST CREATED OR SOMEWHERE ELSE? A form module is the module that is attached to the form. When you click on the Code Builder option in the event procedure, it opens the form's module in the VB Edtior. Nothing magic about it. So, the answer is yes. Private Sub AddVisitor(VisitDept As String) Dim strSQL As String On Error GoTo AddVisitor_Error strSQL = "INSERT INTO tblDeptVisits ( Dept, VisitTime ) " & _ "SELECT """ & VisitCity & """ AS Expr1, #" & Now() & "# AS Expr2;" CurrentDb.Execute strSQL, dbFailOnError The Now(), that is what i guess is making the date do strange things. The results then populate a table directly. "Klatuu" wrote: That format is for a US date, don't you want "dd\/mm\/yyyy"? Where do you want to present the results? -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: Do you think this code would help, Function SQLDate(vDate As Variant) As String If IsDate(vDate) Then SQLDate = "#" & Format$(vDate, "mm\/dd\/yyyy") & "#" End If End Function It was taken from a website that indicates that dates in SQL seem to stick to American formats and this code above should allow for other countries, namely UK to capture the correct format. I have tried to call it from the module you gave me, but wasn't sure how or if it would work. If I was in America this thread would have finished a long time ago |
#20
|
|||
|
|||
Customer Count Database
The module you gave me here (part of)..
Private Sub AddVisitor(VisitDept As String) Dim strSQL As String On Error GoTo AddVisitor_Error strSQL = "INSERT INTO tblDeptVisits ( Dept, VisitTime ) " & _ "SELECT """ & VisitCity & """ AS Expr1, #" & Now() & "# AS Expr2;" CurrentDb.Execute strSQL, dbFailOnError The Now(), that is what i guess is making the date do strange things. The results then populate a table directly. "Klatuu" wrote: That format is for a US date, don't you want "dd\/mm\/yyyy"? Where do you want to present the results? -- Dave Hargis, Microsoft Access MVP "Mr-Re Man" wrote: Do you think this code would help, Function SQLDate(vDate As Variant) As String If IsDate(vDate) Then SQLDate = "#" & Format$(vDate, "mm\/dd\/yyyy") & "#" End If End Function It was taken from a website that indicates that dates in SQL seem to stick to American formats and this code above should allow for other countries, namely UK to capture the correct format. I have tried to call it from the module you gave me, but wasn't sure how or if it would work. If I was in America this thread would have finished a long time ago |
Thread Tools | |
Display Modes | |
|
|