Recent content by JaneInMA

I am pretty sure the reason Access is having issues with this is because I am doing my joins within the subquery calling the lookup tables twice. I have rewritten the query giving the subquery an alias and setting up the joins using the subquery product. Here is the new query TRANSFORM...

Makes me wonder if it is a microsoft feature. The query does pull four tables, two of which are massive and dbfs, together using a union query in a subquery for a crosstab. Perhaps the complexity breaks the sql. It doesnt even realise this is a union query because of it being nested in the...

I write the query and it runs fine, I then save it and exit. I then try to open the query in the application windows and it fails. If I then open it in design view it goes to the QBE window (with all issues) but if I go to the sql change the square brackets around the subquery to normal brackets...

I am writing a series of crosstab queries like the one below TRANSFORM Nz(Count([macs-US].[loggedBy]),0) AS CountOfREFNUM SELECT Format([ALRTDATE],"yyyy-mm") AS monthu, Format([ALRTDATE],"mmm-yyyy") AS [Month] FROM (SELECT [macs-US].LOGGEDBY, [macs-US].ALRTDATE, [macs-US].COMPCODE...

You have to take the data and clean it up to be more user friendly. It really isnt a big deal to take the data and just change the header row if it is in the right order. I export to excel then change the header row before graphing the results. In fact for my monthly reports I tend to drop the...

try this TRANSFORM Sum(tblDemandAdj.Qty) AS SumOfQty SELECT tblDemandAdj.Vendor, tblDemandAdj.[Part Number] FROM tblDemandAdj WHERE (((tblDemandAdj.ConvertedDate)>Now()+84)) GROUP BY tblDemandAdj.Vendor, tblDemandAdj.[Part Number] PIVOT Format([ConvertedDate],"yy/mm"); that will sort...

Are you sorting by month? if so sorting by yy-mmm would give "03-feb" and by yy-mm give "03-02". The first gets sorted alphanumerically, the second by the month. Any time the date is described by words rather than numbers it sorts it alphanumerically. So format the date as...

Format your date as "yy-mm";- suddenly your dates sort by date. Go to the properties of the date make sure that it is not hard coded to look for "jan", "feb" etc. Check the sql to be certain

yes but code is only useful when you learn about it and how to use it. Your tip to look in this direction was helpful.

Wildhare, just a brilliant example of how a small tip can help a lot. Thanks, I feel dateserial may be my new favourite piece of code. It will make my crosstab queries self sustaining!

If the table can not be opened because of a corrupted record it cannot be copied. Make sure this can not be repaired by repairing the database. If it can't, create a maketable query using your bad table adding all the records. You will find Access unable to copy your corrupted record giving you...

Open your query and right click on the properties for the date, type in the order you want the columns. Ie I Use mmm-yy as a format and I paste the following into the area for column headings...

The above solution gives a month time period. What you need is to format the birthdate by month and day in the field part of the query builder ie Expr1: Format([birthdate],"mm-dd") then in the criteria apply similar formatting Between Format((Date()+7),"mm-dd") And...

I am sure there is a simple trick here I am missing so I am asking for help. I have worked with Access97 for 2 years now and just been moved to Access2K with no books to reference. In 97 when I add a subform when designing the form, it appears as a flat reference which you can double click on...

This has been done in the MS Access Handbook here is the code I use Function OpenworkDays(OpenDate, Optional CloseDate) As Integer Dim OpDate As Date Dim ClDate As Date Dim i As Date 'counter Dim WrkDays As Integer If Not IsDate(OpenDate) Then MsgBox "Valid opendate not...