Hello
Would it be possible for someone to explain to me how to program a variable length text fields in a table, as opposed to a fixed length field?
Also - what would be the disadvantage of using this type of field ?
Thanks
Shellie
I have a database that is used for tracking changes to numerous courses. Part of this database create a unique tracking number for each course problem developed. Currently I have the form do a comparison using the highest most number to compare against the current number assigned and prevent the form from saving until the number is incremented and not a duplicate, it would be a lot easier if I could just have it increment plus 1. I have seen various answers but they all seem to depend on the alpha portion of the field being a set value, in my instance it is variable in length. The only part that is fixed is the last four characters to the right which are the numeric portion I would like to increment. For example the field can equal:
QACP-M-PIQ-6059
QACP-M-PREF-6002
how to extract just the numeric portion, increment it by one and save?
I can get the number of records to be returned but I want to run a condition if the records returned is zero. To do this, I need to put the number returned into a variable but I haven't been find a way.
For instance, this code does work:
SQL = "SELECT Count(*) AS Records FROM Table1 GROUP BY Table1.Quantity HAVING (((Table1.Quantity)=4));"
conDatabase.Execute SQL
But I want to store the count as a variable. What I want is something that look this (it doesn't work but I just want to give an idea what I'm looking for)
SQL = "SELECT Count(*) AS Records FROM Table1 GROUP BY Table1.Quantity HAVING (((Table1.Quantity)=4));"
Dim hold as Integer
hold = conDatabase.Execute SQL
If(hold = 0) Then
'tell them nothing returned
End If
So how can I go about this?
Update: I see that someone posted a solution a few threads down using queries which did work. Can I still still do the same thing with SQL strings?
scratch
I need to extract a specific number of records into a table using a MakeTable or Append command using a temp variable, e.g. TempK&SA. Previously on the forum I was shown how code could be added to the OnOpen function to use a temp variable to select a specific number of records to report. ACCESS does not have the OnOpen function in the design view of a query like in the report. It does allow a SELECT TOP but only with fixed variables or percents (e.g. 25 in the code below).
The beginning code for the make table query (where 25 is the number of records added) is:
INSERT INTO [Output] ( RndNo, PointBiserial, BloomsTax, DateRevised, Exam1, Status, Exam2, Exam3, Exam4, [NCCPAKnowledge&Skills] )
SELECT TOP 25 TestBank.RndNo, TestBank.PointBiserial, TestBank.BloomsTax, TestBank.DateRevised, TestBank.Exam1, TestBank.Status, TestBank.Exam2, TestBank.Exam3, TestBank.Exam4, TestBank.[NCCPAKnowledge&Skills], *
FROM TestBank
WHERE (((TestBank.PointBiserial) Is Null Or (TestBank.PointBiserial) Between [TempVars]![TempPointBiserialLow] And .....
how to modify the code to allow a temp variable to determine the number of records to append to another table would be gratefully received. (This process then is repeated for a total of 7 append tables with different temp variables.)
I am creating a 2 level report to confirm an order. Main report already created, runs successfully called as subform/subreport under "OrderDetails" form. Linked to master using Order.ID. There are two versions of the confirmation report that have different layouts for different program types.
The hangup comes when I try to add a "Class Dates" subreport. It lists dates of individual classes and Skip dates. I have created the subreport as "srClassDates". When I add it to the main report, it lists the records. However, when I try to link it to the Main report, an error message box appears with the "object variable or With block variable not set".
I have tried rebuilding both the main and subreports, rebuilt the query, have not found anything that changes the result.
Linker has been working successfully on other subforms. Report with groupings works fine, but I need data from 2 tables both linked to order.id.
Error 91 - Object variable or With block variable not set
I am getting this error telling me that an object variable is not set.
I know which variable it is but when I step through the debugger it sets the variable and all is fine? Issue is that public variable of a class is not getting set when the VBA Editor is not open?
This code runs fine the FIRST time, however trows up a message the SECOND time it is run.
The error is on the line ".Range"
I am trying to sort records which have been exported to Excel.
Dim LR As Integer
LR = 5
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set wbRef = xlApp.Workbooks.Add
With wbRef
wbRef.Activate
.Worksheets("Sheet1").Activate
With ActiveSheet
.Range("A2", .Cells(LR, "O").End(xlUp)).Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes
End With
end With
I look at a lot of files to see when they were last updated. I wanted to write a generic procedure to manage that so ..
Code:
Public fDate As Variant
Public vField As String
Public vFile As String
'GTSdata
vField = "txt_gts_data"
[Code] ....
What I hoped Me.vField would do is update the date field [txt_gts_data] on my form with the date the file was last saved.
i.e. me. txt_gts_data = fDate
What actually happens is the variable vfield gets updated from "txt_gts_data" to 19/08/2014 then later code falls over because the fieldname is lost .
Me.[vField] corrects itself to me.vField (and does not work)
Me!vfield falls over (cannot find the field vField, not surprising J)
How do I say update the contents of the variable, not the variable itself?
Runtime error '91'
Running Access 2010.
I have two reports running off of the same crosstab query. I copied one report to make the second report, then modified the second report to change the background of column fields satisfying certain conditions. These lines of code were added to the Detail_Format section, in color below. The report with the extra code lines does not error--the original report errors.
Code:
Option Compare Database
' Constant for maximum number of columns EmployeeSales query would
' create plus 1 for a Totals column. Here, you have 9 employees.
Const conTotalColumns = 11
[Code] .....
If I say OK (rather than debug) after the error message, I can then click the button for the report again and it runs without complaint. And, as I said, the report with the added code never errors.
I have a networked database. It is accessed with computers that have both Office 2002, and office 2003. I get the following error message on computers with Office 2003:
"Object variable or With block variable not set"
It happens occasionally and the problem generally fixes itself so I dont think there is anything wrong with my coding.
And when it happens on the computers with office 2003 the ones with office 2002 can open the database fine.
What can be causing this?
Thank you in advance
Good afternoon all,
The following block of code was working great until I came into work this morning. This routine is called from the OnClick event of several combo boxes. When it does, I recieve the error listed above (Object variable or With block variable not set) on line:
If Me.cbxAss_Filter <> "All" Then
Any ideas? I'm running on no sleep in the last 24 hours so I'm sure I'm missing something simple
The code itself checks the contents of a form and builds a string of conditions which I apply to the Form's filter property to filter records.
Code:Public Sub CreateFilter() '************************************************* **** 'Name: CreateFilter 'Purpose: Generate a string to filter the form 'Inputs: None 'Outputs: None 'Instigates: Me.Filter ' 'Updated: 11/30/06 'By: Chris Lounsbury '************************************************* **** 'Vars Dim strFilter Dim lngLength As Long strFilter = Null strFilter = "" 'Each filter box has its own check for contents If Me.cbxAss_Filter <> "All" Then strFilter = "assigned = '" & Me.cbxAss_Filter & "' AND " End If If Me.cbxAction_filter <> "All" Then strFilter = strFilter & "action = '" & Me.cbxAction_filter & "' AND " End If If Me.cbxStatus_filter <> "All" Then strFilter = strFilter & "status_rsrch = '" & Me.cbxStatus_filter & "' AND " End If If Me.Combo34 <> "All" Then strFilter = strFilter & "rims_flags = '" & Me.Combo34 & "' AND " End If If Me.cbxAAMB_filter <> "All" Then strFilter = strFilter & "aamb = '" & Me.cbxAAMB_filter & "' AND " End If 'Check if filter string was built If strFilter = "" Then Me.FilterOn = False Else 'Determine length of Filter String 'minus the trailing ' AND' lngLength = Len(strFilter) - 5 'Chop off ending ' AND' and set the form filter If lngLength <= 0 Then Else Me.Filter = Left(strFilter, lngLength) Me.FilterOn = True End If End If 'debug 'MsgBox (strFilter) End Sub
Um, well hi
I am a VB programmer, and need a database for a diary program i am making. Only i need more then 255 characters in one of my fields.
Is this possible, if not, what are my options.
Thank you
ILMV
I have a fax number field iwhich has a mask. +(000)-00-000-000 in a table of many records.
The problem is that every record has a number in the first part of country code +(001)which must have come there by mistakes, few records have complete and true fax numbers.
I need a query that can Update this Fax_Number field where the length of the string is less than 7 digits.
Some thing like UPDATE Contacts SET Contacts.Fax_Number = ""
WHERE ((Len("Fax_Number")>"6"));
How can i do this. Thanks.
I have a field, integer, that needs to be exactly 6 characters long, no more, no less. How would I set this?:p
View 1 Replies View RelatedHi,
I'm looking for some advice on what the maximum field length is in Access. Which option will provide the maximum length. I am wanting the field to capture both numbers and text.
Any pointers in the right direction would be much appreciated.
Thanks in Advance.
What is the maximum number of characters which can make up a query?
Is it 255?
J-F
is there a way to allows more that 255 chars in to a text field in a mdb? or do i have to do something like save it to a test file then link to the text file (cos i really dont know how to do that :P)
View 3 Replies View RelatedI've notice a situation in a few databases. One is where we track sidewalk construction. There are two called fields LENGTH and WIDTH. In my report I have text box with the control source =[Length]*[Width]. However it returns some bogus number. If I change the field names to something else like Width1, it works fine.
Any ideas?
Thanks,
SKK
How can I extract a string and from a textbox and make out that it is x (here x can be any value, e.g. 15) characters long??
View 1 Replies View RelatedHi
Is it possible to change the default table setting in Access 2000 for the 'Allow Zero Length' property from No to Yes? Or does anyone know any code that can be run to change any existing fields with Allow Zero Length = No to = Yes?
Any assistance would be much appreciated.
Thanks.
Hello,
If I read the documentation then memo field in a Access table can be 65.000 chars long.
I have three memo fields in a linked ODBC table which is filled by a append query from SQL-server database. The text in these field is cut off by 255 chars.
Does somebody know what the cause of this can be?
1. Is there a limitation/property where I can configure the length of Access memo field? DAO? I have read something about this but I don't understand this.
2. How can I trace that everything is send from the source? Maybe it's cut off at the source site. On this moment I don't know something about a Linked tabel.
Is there a sniffer or trace tool so that I can see what kind of data is realy send?
Thanks in advance
Nico
Hi everyone,
I am using a table that stores data and one of the fields is just a text field with a maximum length of 10.
However, when I store the data in the field, if I only enter 5 letters in it, it stores the 5 letter word correctly but followed by some spaces (I assume 5 spaces but I may be wrong.
I cannot figure out why does anyone know why?
If not, then can I trim the field if I use it in a query/report?
Thanks.
Gareth.
All,
I am using an Make-Table query to add many new fields to a Table using Fieldname: "".
However, when I use this, it sets the Allow Zero Length to No.
Is there code that I can use to stop this happening?
I am using this method for multiple fields and across multiple tables.
Your help appreciated,
Jem
I have a query in which I need to pull all values from a field that is exactly 10 characters in length (Alpha or Numeric). Can anyone help me with this?
Thank you!
In a database that holds membership records, I have three years worth of info relating to membership dues paid. I want to extract who has not paid a membership fee for the last three years, so we can strike them off our records, i.e. I have members' personal details, but there are no records in the payments table, nothing has been entered because they just have not paid. Do I run a query looking for a zero length string? Just how do I achieve this? Have I done right in not entering anything?
Help appreciated!
I posted this before, but didnt get any answers
In A2007, has anyone had a problem with checking whether a text field is equal to "" in a query
ie - select * from whatever where targetfield = ""
-----------
this generated a type mismatch (I actually had <is null or ""> in the query definition, but it was the "" it didnt like.