| Author |
 Topic  |
|
|
dayve
Forum Moderator
USA
5820 Posts |
 Posted - 21 February 2006 : 19:01:44
|
I feel silly asking this question, but never had a need for doing it this way.
Is it possible to use MS Access Queries to update a field using an expression without using VBA? The process in which I need to do is quite easily done using VB and VBA, but I am trying to assist someone that wants to just run the query. So for example, there is an expression that was created in the SELECT query that needs to be used in an UPDATE query. Unless I am missing something very obvious, I can't seem to do it.
TIA.
UPDATE: Here is their SQL statement
SELECT s8_prop.PROP_ID, s8_prop.FEDERAL_TAX_NO, s8_prop.PAYEE_NAME, s8_prop.INSP_RESULTS, s8_prop.INSP_DATE, s8_prop.NEXT_INSP, DateAdd("yyyy",+1,DateAdd("m",0,[INSP_DATE])-DatePart("d",DateAdd("m",0,[INSP_DATE]))) AS newNextInsp
FROM s8_prop
WHERE (((s8_prop.INSP_RESULTS)="Pass"));
they want to use the result of newNextInsp to update s8_prop.NEXT_INSP
UPDATE 2: Figured it out. I thought I could use the expression name, but had to use the actual formula instead.
UPDATE s8_prop SET s8_prop.NEXT_INSP = DateAdd("yyyy",+1,DateAdd("m",0,[INSP_DATE])-DatePart("d",DateAdd("m",0,[INSP_DATE])))
WHERE s8_prop.INSP_RESULTS)="Pass"; |
 |
Edited by - dayve on 21 February 2006 19:13:15 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 21 February 2006 : 19:22:00
|
| That statement will never work using SQL alone, cause Access SQL does not support those date functions. You will be able to execute in Access but not from ASP, for example. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 21 February 2006 : 19:22:32
|
quote:
Originally posted by ruirib
That statement will never work using SQL alone, cause Access SQL does not support those date functions.
it did work. I see you modified your response.  |
|
Edited by - dayve on 21 February 2006 19:24:55 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
Posted - 22 February 2006 : 12:05:35
|
| You could use a stored query if the updating would be very limited (ie, same field same way every time). |
Dave Maxwell
Barbershop Harmony Freak |
|
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
Posted - 22 February 2006 : 12:06:19
|
Oops. Just realized you already figured it out.
NEVER MIND! Nothing to see here. Move along...move along... |
Dave Maxwell
Barbershop Harmony Freak |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 22 February 2006 : 16:46:19
|
quote:
Originally posted by davemaxwell
Oops. Just realized you already figured it out.
NEVER MIND! Nothing to see here. Move along...move along...
yeah, thanks. I knew of a handful of solutions that I could have easily done, but as I pointed out, I was doing this for someone that did not want it done any other way but through an Access query. worse case scenario, I would have convinced them to do it another way, but I was interested in the possibility and Access seemed to be able to handle the date functions I needed. |
|
|
|
| |
Topic |
|
Topic Locked
