Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. JaneInMA

    Query becomes corrupt

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

    Query becomes corrupt

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

    Query becomes corrupt

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

    Query becomes corrupt

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

    Crosstab sorts alphanumeric not chronological

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

    Crosstab sorts alphanumeric not chronological

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

    Crosstab sorts alphanumeric not chronological

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

    Crosstab sorts alphanumeric not chronological

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

    this month minus 1

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

    this month minus 1

    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!
  11. JaneInMA

    Bad record freezes table

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

    Crosstab Column Sorting

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

    Birthday alerts

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

    Working with subforms in Access 2000

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

    Remove weekends in the Date Diff function

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

    What does this message mean?

    You need to paste the code- all of it then people can see exactly what the problem is. My guess is taht you have pasted in one function but it calls for another function you have not added in. What does the debug window show you?
  17. JaneInMA

    Is there a way to email a table as an attachment?

    I had heard of a problem of text truncation of memo fields when a table is changed from Access to excel. That is why I was suggesting staying in one format. Yes if you are going to do something endless times figure out a way to do it programatically but this sounds like a one time project where...
  18. JaneInMA

    Is there a way to email a table as an attachment?

    Think simple- real simple - create a database with just that table in it. Email the person who needs the table, attach the single table database as an attachment. They get your data in the db then they simply import the table. Simple is best.
  19. JaneInMA

    Using Msldbusr.dll

    I have been using some of the modules available (from microsoft support and also EZ tools from Database Creations Inc) to get information from the msldbuser.dll. The code I am using now gives the computer information rather than the username. I need a list of users currently logged on listed by...
  20. JaneInMA

    storing usernames as they logon?

    Yes, all this happens after the user has entered the password. If you are trying to set up password timeout say after three attempts my guess is you will have to program that yourself not setting up microsoft security.

Part and Inventory Search

Back
Top