Running & Setting Up 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"))) -- Build a little, test a little. "lindag" wrote: Any suggestions on how to use this formula in...

Well if the DATE field is always after Dec 30, 1899, you could plug that in. If it was a DOB you could plug in a date 200 years ago for nulls. Perhaps you are missing some parentheses to make sure things are working. I would have parentheses around each set of fields like so: AND (([T003-Snapshot Table].[L3]=[Q010-Program-Tracker].[L3]) Or ([T003-Snapshot Table].[L3] is Null AND [Q010-Program-Tracker].[L3] is NULL)) I would try just joining...

The concept of a row number is completely foreign to a table in a relational database. Tables are sets and as such have no intrinsic order. You'll need to specify a sort order which determines which are the top 5 rows you want to retain. Assuming you can do this on a column in the table then you'd use a subquery to return the top 5 rows and restrict the rows deleted to those not returned by the subquery, e.g. DELETE * FROM [Table A]...

Mccloud, You have asked several vague questions without providing your table and field names. I expect if you provide some field and table names as well as enough records and what you expect for results, someone can help you. If you choose to not provide this information, we can only make wags. -- Duane Hookom Microsoft Access MVP "mccloud" wrote: I have a form that a user can enter the number of periods to review up to 24....

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" wrote in message...

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], Transactions.Usercode, Transactions.location FROM Transactions LEFT JOIN Transdata ON...

On Wed, 25 Nov 2009 20:07:20 GMT, "instereo911 via AccessMonster.com" u27979@uwe wrote: Hi, I have a table w/ multiple values on one column seperated out by a ";#" sign. Example: State Number ;#Central;#COVA;# 1 Ideally, what i want to do is the following State Number Central 1 COVA 1 So it breaks it out to two different rows for each of the ;# Is this possible? I hope so because I don't want to redo...

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? -- HTH, Bob Barrows

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...

And what will you do when the powers-that-be decide to add yet another "movement"? Or stop considering one of the existing ones? Your table design is not so much a relational database table as a spreadsheet. Access is a relational database, and is optimized to work with well-normalized data. If you try to feed it 'sheet data, both you and Access will end up working overtime to come up with work-arounds ... oh wait,...