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 |
#1
|
|||
|
|||
Data Changing Issue
Hi
I am running some queries using a form I created and for some strange reason some people are falling off and coming back to the report when I run the queries and then re-run them immidiately after. I am not changing any of my parameters and the data is not changing in the tables I am linked to. Can you help? I am using Access 2003. I have several different queries running as make table queries from Foxpro database linked tables. Within these queries I have one query that has a parameter set for "Married Date" What this does is prompt the user to enter a start date and then I have an expression that calculates the number of months from that "start date" to an actual "Marriage Date" field in one of the tables I am linking from foxpro. The expression looks like this: DateDiff("m",[smf]![smfwedding],[WEDDINGDATE]) ** WEDDINGDATE is my "start date", SMF!SMFWEDDING is a field from table from foxpro** Would this be the reason data is changing like this? Help. Thanks |
#2
|
|||
|
|||
Data Changing Issue
Depends where this statement is used.
If inside a query, the table sm should be part of the FROM clause (or, graphically, in the upper half of the query designer) and the preferred syntax is a dot, not a bang. DateDiff("m", smf.smfwedding, WEDDINGDATE) If in a form or in a report, then the table value should be 'bound' to a control (can be invisible, if you prefer so), and the preferred syntax would be like: DateDiff("m", Me.ControlName , WEDDINGDATE) Note that even if your table name is sm, there is no VBA-object that is open, by default, with that name. So, in the immediate debug window, as example, ? table1!myid is very unlikely to print some value of myid present in table1... unless table1 is an existing VBA object, at that moment. Hoping it may help, Vanderghast, Access MVP "Karen" wrote in message ... Hi I am running some queries using a form I created and for some strange reason some people are falling off and coming back to the report when I run the queries and then re-run them immidiately after. I am not changing any of my parameters and the data is not changing in the tables I am linked to. Can you help? I am using Access 2003. I have several different queries running as make table queries from Foxpro database linked tables. Within these queries I have one query that has a parameter set for "Married Date" What this does is prompt the user to enter a start date and then I have an expression that calculates the number of months from that "start date" to an actual "Marriage Date" field in one of the tables I am linking from foxpro. The expression looks like this: DateDiff("m",[smf]![smfwedding],[WEDDINGDATE]) ** WEDDINGDATE is my "start date", SMF!SMFWEDDING is a field from table from foxpro** Would this be the reason data is changing like this? Help. Thanks |
#3
|
|||
|
|||
Data Changing Issue
Hi Michel - Thanks for the info. To clarify (I am not an expert in Access,
(yet)) but I understood what you said below except for "Note that even if your table name is sm, there is no VBA-object that is open, by default, with that name. So, in the immediate debug window, as example, ? table1!myid is very unlikely to print some value of myid present in table1... unless table1 is an existing VBA object, at that moment." Can you please clarify this? I am verifying the data now with your suggestion on changing the expression within the query. Hoping the data is correct. Thanks "Michel Walsh" wrote: Depends where this statement is used. If inside a query, the table sm should be part of the FROM clause (or, graphically, in the upper half of the query designer) and the preferred syntax is a dot, not a bang. DateDiff("m", smf.smfwedding, WEDDINGDATE) If in a form or in a report, then the table value should be 'bound' to a control (can be invisible, if you prefer so), and the preferred syntax would be like: DateDiff("m", Me.ControlName , WEDDINGDATE) Note that even if your table name is sm, there is no VBA-object that is open, by default, with that name. So, in the immediate debug window, as example, ? table1!myid is very unlikely to print some value of myid present in table1... unless table1 is an existing VBA object, at that moment. Hoping it may help, Vanderghast, Access MVP "Karen" wrote in message ... Hi I am running some queries using a form I created and for some strange reason some people are falling off and coming back to the report when I run the queries and then re-run them immidiately after. I am not changing any of my parameters and the data is not changing in the tables I am linked to. Can you help? I am using Access 2003. I have several different queries running as make table queries from Foxpro database linked tables. Within these queries I have one query that has a parameter set for "Married Date" What this does is prompt the user to enter a start date and then I have an expression that calculates the number of months from that "start date" to an actual "Marriage Date" field in one of the tables I am linking from foxpro. The expression looks like this: DateDiff("m",[smf]![smfwedding],[WEDDINGDATE]) ** WEDDINGDATE is my "start date", SMF!SMFWEDDING is a field from table from foxpro** Would this be the reason data is changing like this? Help. Thanks |
#4
|
|||
|
|||
Data Changing Issue
I mean that even if the table exists on the hard disk, you cannot reach it
simply by using its name, *if* you are in VBA. In fact, in VBA, you can read the data of your table from a recordset. Note that if you are using a Form or a Report, you may give the name of the table as record-source (see this property), without having to explicitly open it in VBA. Access will just open a recordset for you, in that case. You can reach that recordset, in VBA code behind the form, with the expression: Me.Recordset. If you are in a query, rather than being in VBA, the table name must be in the FROM clause. Otherwise, the table won't be OPEN to you, even if it exists on the hard disk. (Sure, you can also use Access end user interface to reach your data, such as seeing a table in data-view, as example, but your question leads me to think you were using either VBA code, either a query.) Vanderghast, Access MVP "Karen" wrote in message ... Hi Michel - Thanks for the info. To clarify (I am not an expert in Access, (yet)) but I understood what you said below except for "Note that even if your table name is sm, there is no VBA-object that is open, by default, with that name. So, in the immediate debug window, as example, ? table1!myid is very unlikely to print some value of myid present in table1... unless table1 is an existing VBA object, at that moment." Can you please clarify this? I am verifying the data now with your suggestion on changing the expression within the query. Hoping the data is correct. Thanks "Michel Walsh" wrote: Depends where this statement is used. If inside a query, the table sm should be part of the FROM clause (or, graphically, in the upper half of the query designer) and the preferred syntax is a dot, not a bang. DateDiff("m", smf.smfwedding, WEDDINGDATE) If in a form or in a report, then the table value should be 'bound' to a control (can be invisible, if you prefer so), and the preferred syntax would be like: DateDiff("m", Me.ControlName , WEDDINGDATE) Note that even if your table name is sm, there is no VBA-object that is open, by default, with that name. So, in the immediate debug window, as example, ? table1!myid is very unlikely to print some value of myid present in table1... unless table1 is an existing VBA object, at that moment. Hoping it may help, Vanderghast, Access MVP "Karen" wrote in message ... Hi I am running some queries using a form I created and for some strange reason some people are falling off and coming back to the report when I run the queries and then re-run them immidiately after. I am not changing any of my parameters and the data is not changing in the tables I am linked to. Can you help? I am using Access 2003. I have several different queries running as make table queries from Foxpro database linked tables. Within these queries I have one query that has a parameter set for "Married Date" What this does is prompt the user to enter a start date and then I have an expression that calculates the number of months from that "start date" to an actual "Marriage Date" field in one of the tables I am linking from foxpro. The expression looks like this: DateDiff("m",[smf]![smfwedding],[WEDDINGDATE]) ** WEDDINGDATE is my "start date", SMF!SMFWEDDING is a field from table from foxpro** Would this be the reason data is changing like this? Help. Thanks |
#5
|
|||
|
|||
Data Changing Issue
Hi
OK, what you suggested did not work. I updated my expression to what you indicated and re-ran all the queries (using form) and when I saw the results on my report the numbers kept changing again. Could it be something else? Thanks "Michel Walsh" wrote: I mean that even if the table exists on the hard disk, you cannot reach it simply by using its name, *if* you are in VBA. In fact, in VBA, you can read the data of your table from a recordset. Note that if you are using a Form or a Report, you may give the name of the table as record-source (see this property), without having to explicitly open it in VBA. Access will just open a recordset for you, in that case. You can reach that recordset, in VBA code behind the form, with the expression: Me.Recordset. If you are in a query, rather than being in VBA, the table name must be in the FROM clause. Otherwise, the table won't be OPEN to you, even if it exists on the hard disk. (Sure, you can also use Access end user interface to reach your data, such as seeing a table in data-view, as example, but your question leads me to think you were using either VBA code, either a query.) Vanderghast, Access MVP "Karen" wrote in message ... Hi Michel - Thanks for the info. To clarify (I am not an expert in Access, (yet)) but I understood what you said below except for "Note that even if your table name is sm, there is no VBA-object that is open, by default, with that name. So, in the immediate debug window, as example, ? table1!myid is very unlikely to print some value of myid present in table1... unless table1 is an existing VBA object, at that moment." Can you please clarify this? I am verifying the data now with your suggestion on changing the expression within the query. Hoping the data is correct. Thanks "Michel Walsh" wrote: Depends where this statement is used. If inside a query, the table sm should be part of the FROM clause (or, graphically, in the upper half of the query designer) and the preferred syntax is a dot, not a bang. DateDiff("m", smf.smfwedding, WEDDINGDATE) If in a form or in a report, then the table value should be 'bound' to a control (can be invisible, if you prefer so), and the preferred syntax would be like: DateDiff("m", Me.ControlName , WEDDINGDATE) Note that even if your table name is sm, there is no VBA-object that is open, by default, with that name. So, in the immediate debug window, as example, ? table1!myid is very unlikely to print some value of myid present in table1... unless table1 is an existing VBA object, at that moment. Hoping it may help, Vanderghast, Access MVP "Karen" wrote in message ... Hi I am running some queries using a form I created and for some strange reason some people are falling off and coming back to the report when I run the queries and then re-run them immidiately after. I am not changing any of my parameters and the data is not changing in the tables I am linked to. Can you help? I am using Access 2003. I have several different queries running as make table queries from Foxpro database linked tables. Within these queries I have one query that has a parameter set for "Married Date" What this does is prompt the user to enter a start date and then I have an expression that calculates the number of months from that "start date" to an actual "Marriage Date" field in one of the tables I am linking from foxpro. The expression looks like this: DateDiff("m",[smf]![smfwedding],[WEDDINGDATE]) ** WEDDINGDATE is my "start date", SMF!SMFWEDDING is a field from table from foxpro** Would this be the reason data is changing like this? Help. Thanks |
#6
|
|||
|
|||
Data Changing Issue
Maybe I should simplify my problem by asking this question.
I have a query with a married date of students. I want to a list of people that have been married =24 months (2 years). I do not want the 2 years to be calculated from today's date (Now()). I want to enter a date that it should calculate 2 years from. How would you recommend I do this ? Thanks "Michel Walsh" wrote: I mean that even if the table exists on the hard disk, you cannot reach it simply by using its name, *if* you are in VBA. In fact, in VBA, you can read the data of your table from a recordset. Note that if you are using a Form or a Report, you may give the name of the table as record-source (see this property), without having to explicitly open it in VBA. Access will just open a recordset for you, in that case. You can reach that recordset, in VBA code behind the form, with the expression: Me.Recordset. If you are in a query, rather than being in VBA, the table name must be in the FROM clause. Otherwise, the table won't be OPEN to you, even if it exists on the hard disk. (Sure, you can also use Access end user interface to reach your data, such as seeing a table in data-view, as example, but your question leads me to think you were using either VBA code, either a query.) Vanderghast, Access MVP "Karen" wrote in message ... Hi Michel - Thanks for the info. To clarify (I am not an expert in Access, (yet)) but I understood what you said below except for "Note that even if your table name is sm, there is no VBA-object that is open, by default, with that name. So, in the immediate debug window, as example, ? table1!myid is very unlikely to print some value of myid present in table1... unless table1 is an existing VBA object, at that moment." Can you please clarify this? I am verifying the data now with your suggestion on changing the expression within the query. Hoping the data is correct. Thanks "Michel Walsh" wrote: Depends where this statement is used. If inside a query, the table sm should be part of the FROM clause (or, graphically, in the upper half of the query designer) and the preferred syntax is a dot, not a bang. DateDiff("m", smf.smfwedding, WEDDINGDATE) If in a form or in a report, then the table value should be 'bound' to a control (can be invisible, if you prefer so), and the preferred syntax would be like: DateDiff("m", Me.ControlName , WEDDINGDATE) Note that even if your table name is sm, there is no VBA-object that is open, by default, with that name. So, in the immediate debug window, as example, ? table1!myid is very unlikely to print some value of myid present in table1... unless table1 is an existing VBA object, at that moment. Hoping it may help, Vanderghast, Access MVP "Karen" wrote in message ... Hi I am running some queries using a form I created and for some strange reason some people are falling off and coming back to the report when I run the queries and then re-run them immidiately after. I am not changing any of my parameters and the data is not changing in the tables I am linked to. Can you help? I am using Access 2003. I have several different queries running as make table queries from Foxpro database linked tables. Within these queries I have one query that has a parameter set for "Married Date" What this does is prompt the user to enter a start date and then I have an expression that calculates the number of months from that "start date" to an actual "Marriage Date" field in one of the tables I am linking from foxpro. The expression looks like this: DateDiff("m",[smf]![smfwedding],[WEDDINGDATE]) ** WEDDINGDATE is my "start date", SMF!SMFWEDDING is a field from table from foxpro** Would this be the reason data is changing like this? Help. Thanks |
#7
|
|||
|
|||
Data Changing Issue
In the query, type:
AgeInMonth: DateDiff("m", fieldName, FORMS!formName!TextBoxName) - DatePart("d", FORMS!formName!TextBoxName) = DatePart("d", fieldName) where fieldName is the field name holding the marriage date formName is the open form name with the control having the date you supplied textBoxName is the control name, in the formName, which hold the date you have to supply In the criteria line, add = 24 Hoping it may help, Vanderghast, Access MVP "Karen" wrote in message ... Maybe I should simplify my problem by asking this question. I have a query with a married date of students. I want to a list of people that have been married =24 months (2 years). I do not want the 2 years to be calculated from today's date (Now()). I want to enter a date that it should calculate 2 years from. How would you recommend I do this ? Thanks "Michel Walsh" wrote: I mean that even if the table exists on the hard disk, you cannot reach it simply by using its name, *if* you are in VBA. In fact, in VBA, you can read the data of your table from a recordset. Note that if you are using a Form or a Report, you may give the name of the table as record-source (see this property), without having to explicitly open it in VBA. Access will just open a recordset for you, in that case. You can reach that recordset, in VBA code behind the form, with the expression: Me.Recordset. If you are in a query, rather than being in VBA, the table name must be in the FROM clause. Otherwise, the table won't be OPEN to you, even if it exists on the hard disk. (Sure, you can also use Access end user interface to reach your data, such as seeing a table in data-view, as example, but your question leads me to think you were using either VBA code, either a query.) Vanderghast, Access MVP "Karen" wrote in message ... Hi Michel - Thanks for the info. To clarify (I am not an expert in Access, (yet)) but I understood what you said below except for "Note that even if your table name is sm, there is no VBA-object that is open, by default, with that name. So, in the immediate debug window, as example, ? table1!myid is very unlikely to print some value of myid present in table1... unless table1 is an existing VBA object, at that moment." Can you please clarify this? I am verifying the data now with your suggestion on changing the expression within the query. Hoping the data is correct. Thanks "Michel Walsh" wrote: Depends where this statement is used. If inside a query, the table sm should be part of the FROM clause (or, graphically, in the upper half of the query designer) and the preferred syntax is a dot, not a bang. DateDiff("m", smf.smfwedding, WEDDINGDATE) If in a form or in a report, then the table value should be 'bound' to a control (can be invisible, if you prefer so), and the preferred syntax would be like: DateDiff("m", Me.ControlName , WEDDINGDATE) Note that even if your table name is sm, there is no VBA-object that is open, by default, with that name. So, in the immediate debug window, as example, ? table1!myid is very unlikely to print some value of myid present in table1... unless table1 is an existing VBA object, at that moment. Hoping it may help, Vanderghast, Access MVP "Karen" wrote in message ... Hi I am running some queries using a form I created and for some strange reason some people are falling off and coming back to the report when I run the queries and then re-run them immidiately after. I am not changing any of my parameters and the data is not changing in the tables I am linked to. Can you help? I am using Access 2003. I have several different queries running as make table queries from Foxpro database linked tables. Within these queries I have one query that has a parameter set for "Married Date" What this does is prompt the user to enter a start date and then I have an expression that calculates the number of months from that "start date" to an actual "Marriage Date" field in one of the tables I am linking from foxpro. The expression looks like this: DateDiff("m",[smf]![smfwedding],[WEDDINGDATE]) ** WEDDINGDATE is my "start date", SMF!SMFWEDDING is a field from table from foxpro** Would this be the reason data is changing like this? Help. Thanks |
#8
|
|||
|
|||
Data Changing Issue
Hi Michel -
Thanks for the quick response. OK, I tried what you indicated below. Here it is: DateDiff("m",[smf]![smfwedding],[FORMS]![FRONTVIEW]![TEXT92])-DatePart("d",[FORMS]![FRONTVIEW]![TEXT92])=DatePart("d",[smf]![smfwedding]) Unfortunetly, it is not working. Now it is giving me 0 records. Just so I explain what I did; (Making sure I understood correctly) I added an unbound text box to the form. In the control source added expression =[Startdate] which is TEXT92 In the query, on the field line, I added the above expression. In the criteria line I added the =24 This did not work. Am I doing something else wrong? Thanks for the patience and help. "Michel Walsh" wrote: In the query, type: AgeInMonth: DateDiff("m", fieldName, FORMS!formName!TextBoxName) - DatePart("d", FORMS!formName!TextBoxName) = DatePart("d", fieldName) where fieldName is the field name holding the marriage date formName is the open form name with the control having the date you supplied textBoxName is the control name, in the formName, which hold the date you have to supply In the criteria line, add = 24 Hoping it may help, Vanderghast, Access MVP "Karen" wrote in message ... Maybe I should simplify my problem by asking this question. I have a query with a married date of students. I want to a list of people that have been married =24 months (2 years). I do not want the 2 years to be calculated from today's date (Now()). I want to enter a date that it should calculate 2 years from. How would you recommend I do this ? Thanks "Michel Walsh" wrote: I mean that even if the table exists on the hard disk, you cannot reach it simply by using its name, *if* you are in VBA. In fact, in VBA, you can read the data of your table from a recordset. Note that if you are using a Form or a Report, you may give the name of the table as record-source (see this property), without having to explicitly open it in VBA. Access will just open a recordset for you, in that case. You can reach that recordset, in VBA code behind the form, with the expression: Me.Recordset. If you are in a query, rather than being in VBA, the table name must be in the FROM clause. Otherwise, the table won't be OPEN to you, even if it exists on the hard disk. (Sure, you can also use Access end user interface to reach your data, such as seeing a table in data-view, as example, but your question leads me to think you were using either VBA code, either a query.) Vanderghast, Access MVP "Karen" wrote in message ... Hi Michel - Thanks for the info. To clarify (I am not an expert in Access, (yet)) but I understood what you said below except for "Note that even if your table name is sm, there is no VBA-object that is open, by default, with that name. So, in the immediate debug window, as example, ? table1!myid is very unlikely to print some value of myid present in table1... unless table1 is an existing VBA object, at that moment." Can you please clarify this? I am verifying the data now with your suggestion on changing the expression within the query. Hoping the data is correct. Thanks "Michel Walsh" wrote: Depends where this statement is used. If inside a query, the table sm should be part of the FROM clause (or, graphically, in the upper half of the query designer) and the preferred syntax is a dot, not a bang. DateDiff("m", smf.smfwedding, WEDDINGDATE) If in a form or in a report, then the table value should be 'bound' to a control (can be invisible, if you prefer so), and the preferred syntax would be like: DateDiff("m", Me.ControlName , WEDDINGDATE) Note that even if your table name is sm, there is no VBA-object that is open, by default, with that name. So, in the immediate debug window, as example, ? table1!myid is very unlikely to print some value of myid present in table1... unless table1 is an existing VBA object, at that moment. Hoping it may help, Vanderghast, Access MVP "Karen" wrote in message ... Hi I am running some queries using a form I created and for some strange reason some people are falling off and coming back to the report when I run the queries and then re-run them immidiately after. I am not changing any of my parameters and the data is not changing in the tables I am linked to. Can you help? I am using Access 2003. I have several different queries running as make table queries from Foxpro database linked tables. Within these queries I have one query that has a parameter set for "Married Date" What this does is prompt the user to enter a start date and then I have an expression that calculates the number of months from that "start date" to an actual "Marriage Date" field in one of the tables I am linking from foxpro. The expression looks like this: DateDiff("m",[smf]![smfwedding],[WEDDINGDATE]) ** WEDDINGDATE is my "start date", SMF!SMFWEDDING is a field from table from foxpro** Would this be the reason data is changing like this? Help. Thanks |
#9
|
|||
|
|||
Data Changing Issue
Can you post the SQL statement appearing in the SQL view of the query?
Vanderghast, Access MVP "Karen" wrote in message ... Hi Michel - Thanks for the quick response. OK, I tried what you indicated below. Here it is: DateDiff("m",[smf]![smfwedding],[FORMS]![FRONTVIEW]![TEXT92])-DatePart("d",[FORMS]![FRONTVIEW]![TEXT92])=DatePart("d",[smf]![smfwedding]) Unfortunetly, it is not working. Now it is giving me 0 records. Just so I explain what I did; (Making sure I understood correctly) I added an unbound text box to the form. In the control source added expression =[Startdate] which is TEXT92 In the query, on the field line, I added the above expression. In the criteria line I added the =24 This did not work. Am I doing something else wrong? Thanks for the patience and help. "Michel Walsh" wrote: In the query, type: AgeInMonth: DateDiff("m", fieldName, FORMS!formName!TextBoxName) - DatePart("d", FORMS!formName!TextBoxName) = DatePart("d", fieldName) where fieldName is the field name holding the marriage date formName is the open form name with the control having the date you supplied textBoxName is the control name, in the formName, which hold the date you have to supply In the criteria line, add = 24 Hoping it may help, Vanderghast, Access MVP "Karen" wrote in message ... Maybe I should simplify my problem by asking this question. I have a query with a married date of students. I want to a list of people that have been married =24 months (2 years). I do not want the 2 years to be calculated from today's date (Now()). I want to enter a date that it should calculate 2 years from. How would you recommend I do this ? Thanks "Michel Walsh" wrote: I mean that even if the table exists on the hard disk, you cannot reach it simply by using its name, *if* you are in VBA. In fact, in VBA, you can read the data of your table from a recordset. Note that if you are using a Form or a Report, you may give the name of the table as record-source (see this property), without having to explicitly open it in VBA. Access will just open a recordset for you, in that case. You can reach that recordset, in VBA code behind the form, with the expression: Me.Recordset. If you are in a query, rather than being in VBA, the table name must be in the FROM clause. Otherwise, the table won't be OPEN to you, even if it exists on the hard disk. (Sure, you can also use Access end user interface to reach your data, such as seeing a table in data-view, as example, but your question leads me to think you were using either VBA code, either a query.) Vanderghast, Access MVP "Karen" wrote in message ... Hi Michel - Thanks for the info. To clarify (I am not an expert in Access, (yet)) but I understood what you said below except for "Note that even if your table name is sm, there is no VBA-object that is open, by default, with that name. So, in the immediate debug window, as example, ? table1!myid is very unlikely to print some value of myid present in table1... unless table1 is an existing VBA object, at that moment." Can you please clarify this? I am verifying the data now with your suggestion on changing the expression within the query. Hoping the data is correct. Thanks "Michel Walsh" wrote: Depends where this statement is used. If inside a query, the table sm should be part of the FROM clause (or, graphically, in the upper half of the query designer) and the preferred syntax is a dot, not a bang. DateDiff("m", smf.smfwedding, WEDDINGDATE) If in a form or in a report, then the table value should be 'bound' to a control (can be invisible, if you prefer so), and the preferred syntax would be like: DateDiff("m", Me.ControlName , WEDDINGDATE) Note that even if your table name is sm, there is no VBA-object that is open, by default, with that name. So, in the immediate debug window, as example, ? table1!myid is very unlikely to print some value of myid present in table1... unless table1 is an existing VBA object, at that moment. Hoping it may help, Vanderghast, Access MVP "Karen" wrote in message ... Hi I am running some queries using a form I created and for some strange reason some people are falling off and coming back to the report when I run the queries and then re-run them immidiately after. I am not changing any of my parameters and the data is not changing in the tables I am linked to. Can you help? I am using Access 2003. I have several different queries running as make table queries from Foxpro database linked tables. Within these queries I have one query that has a parameter set for "Married Date" What this does is prompt the user to enter a start date and then I have an expression that calculates the number of months from that "start date" to an actual "Marriage Date" field in one of the tables I am linking from foxpro. The expression looks like this: DateDiff("m",[smf]![smfwedding],[WEDDINGDATE]) ** WEDDINGDATE is my "start date", SMF!SMFWEDDING is a field from table from foxpro** Would this be the reason data is changing like this? Help. Thanks |
#10
|
|||
|
|||
Data Changing Issue
Sure. Here it is. Thanks
SELECT smf.smfnmfid, smf.smflast, smf.smffirsth, smf.smfentrdt3, Count([No Kollel-NLF].nlftype) AS CountOfnlftype, [No Kollel -SSF 2]!SumOfSSFMonths/12 AS SSFYears, DateDiff("m",smf.smfentrdt3,Date()) AS SMFMonth, [WEDDINGDATE] AS Chosenmarrieddate, DateDiff("m",smf.smfwedding,[WEDDINGDATE]) AS Weddingmonths, smf.smfwedding, [No Kollel-CSF 2].csfregid INTO [Married No Kollel Result] FROM (((smf LEFT JOIN [NO KOLLEL-PAY KOL*] ON smf.smfnmfid = [NO KOLLEL-PAY KOL*].paynmfid) LEFT JOIN [No Kollel-NLF] ON smf.smfnmfid = [No Kollel-NLF].nlfnmfid) LEFT JOIN [No Kollel-CSF 2] ON smf.smfnmfid = [No Kollel-CSF 2].csfnmfid) LEFT JOIN [No Kollel -SSF 2] ON smf.smfnmfid = [No Kollel -SSF 2].ssfnmfid GROUP BY smf.smfnmfid, smf.smflast, smf.smffirsth, smf.smfentrdt3, [No Kollel -SSF 2]!SumOfSSFMonths/12, DateDiff("m",smf.smfentrdt3,Date()), [WEDDINGDATE], DateDiff("m",smf.smfwedding,[WEDDINGDATE]), smf.smfwedding, [No Kollel-CSF 2].csfregid, smf.smfstatus, [NO KOLLEL-PAY KOL*].paynmfid, smf.smfdltdate HAVING ((([No Kollel-CSF 2].csfregid) Is Not Null) AND ((smf.smfstatus)="M") AND (([NO KOLLEL-PAY KOL*].paynmfid) Is Null) AND ((smf.smfdltdate)=#12/30/1899#) AND ((DateDiff("m",[smf].[smfwedding],[WEDDINGDATE]))=24)); "Michel Walsh" wrote: Can you post the SQL statement appearing in the SQL view of the query? Vanderghast, Access MVP "Karen" wrote in message ... Hi Michel - Thanks for the quick response. OK, I tried what you indicated below. Here it is: DateDiff("m",[smf]![smfwedding],[FORMS]![FRONTVIEW]![TEXT92])-DatePart("d",[FORMS]![FRONTVIEW]![TEXT92])=DatePart("d",[smf]![smfwedding]) Unfortunetly, it is not working. Now it is giving me 0 records. Just so I explain what I did; (Making sure I understood correctly) I added an unbound text box to the form. In the control source added expression =[Startdate] which is TEXT92 In the query, on the field line, I added the above expression. In the criteria line I added the =24 This did not work. Am I doing something else wrong? Thanks for the patience and help. "Michel Walsh" wrote: In the query, type: AgeInMonth: DateDiff("m", fieldName, FORMS!formName!TextBoxName) - DatePart("d", FORMS!formName!TextBoxName) = DatePart("d", fieldName) where fieldName is the field name holding the marriage date formName is the open form name with the control having the date you supplied textBoxName is the control name, in the formName, which hold the date you have to supply In the criteria line, add = 24 Hoping it may help, Vanderghast, Access MVP "Karen" wrote in message ... Maybe I should simplify my problem by asking this question. I have a query with a married date of students. I want to a list of people that have been married =24 months (2 years). I do not want the 2 years to be calculated from today's date (Now()). I want to enter a date that it should calculate 2 years from. How would you recommend I do this ? Thanks "Michel Walsh" wrote: I mean that even if the table exists on the hard disk, you cannot reach it simply by using its name, *if* you are in VBA. In fact, in VBA, you can read the data of your table from a recordset. Note that if you are using a Form or a Report, you may give the name of the table as record-source (see this property), without having to explicitly open it in VBA. Access will just open a recordset for you, in that case. You can reach that recordset, in VBA code behind the form, with the expression: Me.Recordset. If you are in a query, rather than being in VBA, the table name must be in the FROM clause. Otherwise, the table won't be OPEN to you, even if it exists on the hard disk. (Sure, you can also use Access end user interface to reach your data, such as seeing a table in data-view, as example, but your question leads me to think you were using either VBA code, either a query.) Vanderghast, Access MVP "Karen" wrote in message ... Hi Michel - Thanks for the info. To clarify (I am not an expert in Access, (yet)) but I understood what you said below except for "Note that even if your table name is sm, there is no VBA-object that is open, by default, with that name. So, in the immediate debug window, as example, ? table1!myid is very unlikely to print some value of myid present in table1... unless table1 is an existing VBA object, at that moment." Can you please clarify this? I am verifying the data now with your suggestion on changing the expression within the query. Hoping the data is correct. Thanks "Michel Walsh" wrote: Depends where this statement is used. If inside a query, the table sm should be part of the FROM clause (or, graphically, in the upper half of the query designer) and the preferred syntax is a dot, not a bang. DateDiff("m", smf.smfwedding, WEDDINGDATE) If in a form or in a report, then the table value should be 'bound' to a control (can be invisible, if you prefer so), and the preferred syntax would be like: DateDiff("m", Me.ControlName , WEDDINGDATE) Note that even if your table name is sm, there is no VBA-object that is open, by default, with that name. So, in the immediate debug window, as example, ? table1!myid is very unlikely to print some value of myid present in table1... unless table1 is an existing VBA object, at that moment. Hoping it may help, Vanderghast, Access MVP "Karen" wrote in message ... Hi I am running some queries using a form I created and for some strange reason some people are falling off and coming back to the report when I run the queries and then re-run them immidiately after. I am not changing any of my parameters and the data is not changing in the tables I am linked to. Can you help? I am using Access 2003. I have several different queries running as make table queries from Foxpro database linked tables. Within these queries I have one query that has a parameter set for "Married Date" What this does is prompt the user to enter a start date and then I have an expression that calculates the number of months from that "start date" to an actual "Marriage Date" field in one of the tables I am linking from foxpro. The expression looks like this: DateDiff("m",[smf]![smfwedding],[WEDDINGDATE]) ** WEDDINGDATE is my "start date", SMF!SMFWEDDING is a field from table from foxpro** Would this be the reason data is changing like this? Help. Thanks |
Thread Tools | |
Display Modes | |
|
|