microsoft.public.access.queries | Forum profile

  Here is a guess -- IIF([MASTER].[JMT_DATE] Is Not Null, "Post Judgment", IIF([MASTER].[JMT_DATE] Is Null AND [MASTER].[SUIT_DATE] Is Not Null, "Post Suit, Pre Judgment", IIF([MASTER.SUIT_DATE] Is Null AND [MASTER].[JMT_DATE] Is Null, "Pre Suit", "Unknown")))

  As far as I know, it is NOT possible to do this in Access. You need to do the appends in order. You could write VBA code to remove all the relationships and then restore/rebuild them after the update is finished. Of course, the danger is that if you did add a record or records that did not comply with the relationship rule, you would generate an error when you tried to rebuild the relationship. John Spencer Access MVP 2002-2005,...

  Joseph Atie wrote: >Im a bit lost as to how to go about this, I have 3 queries that i built in >access query builder which join 4 tables together in order to generate a >report > >What I would like to do is turn those 3 queries into a single sql statement >that can be run from vba. > >Query A >SELECT Transdata.Barcode, Transdata.Out, Transdata.[In],...

  Then my previous reply is appropriate. I would normalize the table structure with a union query like the following where you need to substitute something for .... that relates Curr_Prd to the period field: SELECT item_no, [1] as Qty, DateAdd("m", .... ,Curr_Prd) as Mth FROM dbo_Inv_Buy UNION ALL SELECT item_no, [2], DateAdd("m", .... ,Curr_Prd) FROM dbo_Inv_Buy UNION ALL SELECT item_no, [3],...

  It appears that ACCESS is interpreting your user's input dates as strings, not dates. Either declare the parameters as DateTime type in the query, or format the user's input dates as "formatted dates": PARAMETERS [begin] DateTime, [end] DateTime; SELECT * FROM YourTable WHERE dateadd("h",-8/24, [datetime]) Between [begin] AND [end]; Or SELECT * FROM YourTable WHERE...

  Kou Vang wrote: > How do I translate this into a pass through query? I've been puzzled > over this for a day now? > > SELECT STORET1_TSRFDACT.START_DATE > FROM STORET1_TSRFDACT > WHERE ((Year([start_Date])=2008)); > > I keep getting an error '"YEAR": 'Invalid Identifier' > What is the database you're passing this through to?

  You can try: SELECT Year(date), min( iif( condition1, var1, null)), max( iif(condition1, var1, null)), ... , min( iif(condition2, var2, null)) , ... FROM ... GROUP BY Year(date) that is, remove the conditions from the WHERE clause which are not common to all expressions, since they are not in the iif on which operates the aggregations. Vanderghast, Access MVP "Xmas" <me@privacy.net> wrote in message...

  On Thu, 26 Nov 2009 03:47:07 -0800 (PST), "d3cyph3r@gmail.com" <d3cyph3r@gmail.com> wrote: >Hi I'm trying to run an update query but for some reason it's only >updating some of the records. The weird thing is that the field that >i'm coparting to update is an auto number. >I've checked both tables and the fields seem to be the the same and >the...

  DCL - The query wizards will help you through these. When you select a new query, use the Crosstab Query Wizard for the first one, and the simple Query Wizard for the second one. There is a lot of information in help - both within Access and on-line on these.

  Year is not a valid function in the database that you are trying to use. You need to find out what kind of database and what function you can use in it. For example something like below should work with Oracle. WHERE to_number(to_char([startdate],'YYYY')) = 2008 ; or WHERE to_char([startdate],'YYYY') = '2008' ;