NeoPa - I went back and looked at my original post:
How to hide a table that is made with SQL "CREATE TABLE"?
It didn't generate much discussion... If I recall, I tried altering the table to delete the filed, then added the field again and Access STILL remembered the AutoNumber and didn't reset it back to "1". It may be the BFI method, but it seems to be working...
jbt007
Last Activity: Dec 20 '12, 05:05 PM
Joined: Jul 14 '10
Location: San Antonio, TX, USA
-
NeoPa - The intent IS to keep it the temporary table hidden. I tried to do this using the "hidden" property, but every time an import was done, access would unhide the table. Additionally, in another import (Different report, same temp table) the order of the data is very important, so I added the RecID autonumber field. This allows me to keep the report in exact order as the text file's printed report. The problem with importing multiple...Leave a comment:
-
Final Question...
One Final question...
ADezii - you said "...Within the BVRImp Specification, change the Data Type of the Field that may contain Negative Numbers to TEXT...". I don't seem to have a way of changing the report specification field types. How would one do this? I have uploaded a screen shot of BVRImp spec when I click the edit button...
[imgnothumb]http://bytes.com/attachment...Leave a comment:
-
Solution
All,
Thanks for the great feedback. With y'alls (Ya, a Texan!) help I have created a solution that works. Basic process is as follows:
First create a temp table that is all text fields:
...Code:strSQL = "CREATE TABLE tblTxtRpt ([JVID] Text(5),[SkpFld] Text(2),[Spec] Text(9),[JVTitle] Text(26), " & _ "[OBTot] Text(17),[COTot] Text(13),[BTot] Text(16),[CTot_td]Leave a comment:
-
ADezii - you are correct, are Data Type Conversion Errors for the fields in question. I do not see how changing the data type of the field from Number (Doubel), to Text is going to solve my issue. I need it to be a number. Could you elaborate on your suggestion a bit?Leave a comment:
-
How to get DoCmd.TransferText to Recognise Negative Numbers Using "()"?
Hi All,
System: Access 2010, WinXP
I have a text file I am importing using the following code:
It works fine except where ther are negative numbers in the report being imported. I checked the spec to be sure both "()",...Code:'Import text report into tblBVRRaw table using the BVRImp Spec... DoCmd.TransferText acImportFixed, "BVRImp", "tblBVRRaw", strSourceFile, False
-
So - knowing that changing the ID to a numeric field is not an option, is there a better approach to pulling a sub-set of data between two ranges?Leave a comment:
-
NeoPa - I don't follow your response. If a string is converted to a number with Val('string') or CInt('string'), should not the SQL treat the result as a number, not a string? And if it does not, why did this seem to work?
Thanks for the feedback...Code:... WHERE (((Val(Right(Trim([qryMnth]![ActID]),5)))>[Start Activity ID:]-1 And (Val(Right(Trim([qryMnth]![ActID]),5)))<[End Activity ID:]+1)) ...
Leave a comment:
-
Smiley - Yes I did try your suggestion and the result was the same. The reason for strings is that the ID Code is 10 digits and it may contain an alpha numeric number. The root (right 5) will always be numeric.Leave a comment:
-
I don't know why but this seems to have fixed it:
I guess if you 'force' a calculation in the paramater value, Access decides it's ok to do the math.Code:WHERE (((Val(Right(Trim([qryMnth]![ActID]),5)))>[Start Activity ID:]-1 And (Val(Right(Trim([qryMnth]![ActID]),5)))<[End Activity ID:]+1))
Leave a comment:
-
Ya - I should have said this in my original post. This gives me the same results as the 'Between...' statement.
Code:WHERE (((Val(Right(Trim([qryMnth]![ActID]),5)))>=[Start Activity ID:] And (Val(Right(Trim([qryMnth]![ActID]),5)))<=[End Activity ID:]))
Leave a comment:
-
How to Use 'Between' in SQL Statement?
I have a SLQ Statement using a prompt to get the value of two codes from the user. This should be an easy thing but the result is not what I expect. Can anyone spot the problem? When I type in 8000 for the start ID and 8999 for the end ID, I want only ActID between 8000 and 8999 but I get codes between 8000 and 8999 as well as codes between 80000 and 89999, and codes between 800000 and 899999! Very odd. The ActID field is a text field and all... -
You have to set the Band properties to "Can Shrink" AND every field's txt box to "Can Shrink". This will hid any field that's null. In the query, make sure to set all fields to null that are zero with "IIF([fldName]=0,Null,[fldName])" function.Leave a comment:
-
jbt007 started a topic How do I hide some fields of a record and shrink the detail band of a report?in AccessHow do I hide some fields of a record and shrink the detail band of a report?
Hi all,
I am developing an access report where the fields contain multiple "categories " of cost. A sample is attached.
There are 6 different categories: Quantity, Labor, Hours, Material, Subcontract, & Supplies.
There are several types of cost for each category: Budget, Actual, Earned, Variance, Unit Rate, Wage Rate, etc.
What the client would like is a 8 1/2 x 11 Landscape... -
For whoever wants to know... I found the solution to the original question. The answer was to put "DISTINCTRO W" in the SQL Statement so Access would know to ignor the non-primary key joined table. Here's the solution:
...Code:strSQL = "DELETE DISTINCTROW tblActMap.* " & _ "FROM tblActMap LEFT JOIN " & _ "(SELECT tblMnth.Job, tblMnth.Phase, tblMnth.ActID,Leave a comment:
-
Munkee,
I am no expert, however my suggestion would to extract some data from your db and build the chart you would 'like' to see in XL. Then you will have a model from which to build your pivot chart. For example, what do you want to see for every CorrectiveStart Date? A Count of CorrectiveLinkI D, which would show you how many Corrective items started on that day. Do you want the data grouped by month? (IE: Format(Correcti veStartDate,"yy yy-mm")...Leave a comment:
-
How Do I Suppress Zeros in Access Pivot Chart?
All -
I am trying to track progress against a plan. It seems Access (2003) plots all points (even zeros, nulls, and "" Strings) as zeros for all dates. If you are 50% done with progress, your progress line(s) will take a "dive" and run along the x-axis to the last date.
Here's the $64 question: Who knows how to tell access to ignore the zero (or null) values? (See AccessChartIssu e.jpg for what I...Last edited by jbt007; Jan 25 '11, 08:03 PM. Reason: Shorten question to get some answers... I hope! -
A Solution
Stewart & Rabbit -
Thanks for the input, both of you were very helpful. Using the "DELETE ..." sql code and DoCmd.RunSQL command (Stewart) gave me the error:
Runtime Error: '3027':
Database or Object is Read-Only
I did some help-file reading and from what I can tell the recordset generated from the SQL code creates a read-only recordset (rs.Updatable = False)...Leave a comment:
-
Rabbit - Yes and no. It's much closer than I got. Funny how when you see the answer it seems obvious. I am still getting the following error:
Runtime Error '3128'
Specify the table contining the records you want to delete.
Here's the code I used:
...Code:strSQL = "DELETE tblActMap.Job, tblActMap.Phase, tblActMap.Act, ActList.S_Key " & _ "FROM tblActMap LEFTLeave a comment:
-
How to use a UNION subquery to delete unmatch records?
Hi all - I am trying to create a single sql statement that will delete unmatch recoreds. It is a fairly simple action on the surface, however it involves a subquery that is a UNION. I keep getting errors. Can someone point me in the right direction?. I can create the UNION query, and then create another query that uses the Union query. However, I would really like to combine the two into one SQL statement so it can be placed in VBA without any...
No activity results to display
Leave a comment: