I am a very new access 2003 user and would welcome advice on how to cause the parameter text within [xxxxx...etc]to occupy two lines ( i.e force a carriage return and line feed) and thereby reduce the width of the parameter input box that the user sees. Also are there limits to the number of characters that can be used. I seem to remember a figure of 50 but cannot find it again in any of the books I have.
Many Thanks
Gerry
I am trying to "dummy-proof" an access 2000 DB with a parameter query that asks for the date. I want the parameter to only accept the MED date format (IE - MM/DD/YY) just like the input mask in the Tables design (99->L<LL-00).
Is there any way to do this?
Why is my query limiting itself to only 16 columns? when I tab right for another field, it jumps back to the first field. I have other queries with far more columns than that. My DB size is 3,400 kb.
View 3 Replies View RelatedI m using Access 2010.I m Facing a problem in the undermentioned expression,
OPB: 1*Nz(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<#" & Format([FromDt],"dd-mm-yy") & "#"),0)
when i run the query it gives "Undefined Function 'Format' in Expression" ?
How many nested iifs can access handle in a query..
View 2 Replies View Relatedok i have a invoice form with Subform. Pesently i am doing somthing like this.
Ihave placed a code on the OnEnter event of the subform as such,
If isnull (Me.CustomerId) or me.CustomerID = "" then
msgbox "Please Enter A Valid Customer ID",vbokonly,"Customer ID Needed"
me.customerID.Setfocus
End if
Now what i want is to ammend this code so that before entering inthe Invoice subform the code should check that CustoemrId, CustomerName and Employee Name is not blank. There should be some data in them. How can i make the code checks for the three textboxes instead of one.
Hey buddays,
I have a form with a bunch of Count totals in the footer.
There are 12 different areas with a number of projects in each area. So, I'm counting the total projects, and then I'm counting the instances of an area in the area column...12 times. In the query, I have 12 different Count fields, and in the footer of the form, I have Sum([Count]) for each area and one normal Count field for Total Projects. It works fine, until I have about 11 or more text boxes summing counts. Then I get #Error for all of them.
What's the magic Access limit for form-based functions like this? What's a better/faster/sleeker way of doing this?
I have a form whose data source is a select query, q3, that is built from 2 other select queries. I'll call them q1, q2, and q3. q1 is a parameter query where I enter a "Cutoff Date" that the 3 queries manipulte and generate the desired results that appear in the form. The problem is that I don't know how to capture the parameter "Cutoff Date" from q1 to display on the form.
View 2 Replies View RelatedI want to distribute an app that provides password access for a 6 month period after which time a new password has to be issued by me to the end user for them to continue. This means that I don't lose control of the programme.
Is this possible or are then other ideas to protect the app - I have looked at upsizing to MySQL but it is way beyond me, also I have looked at splitting the app and putting the back end on FTP - but apparently this doesn't work (albeit a good idea as I don't need any other programming skills). Any solution needs to be fairly simple please.
Hey guys, I asked a question before but not sure it's the right place and was difficult to explain anyway so I'll just upload my stuff here.
Basically its a school assignment that's making me so frustrated. progress was fast with tables and forms and reports but there are a few requirements i just dont KNOW how to do, not sure what to search for it the way of google either so it's making life hard.
so requirements are:
Gold memebers can book a max 7 classes, silver 4 and bronze 2.
Members cannot book 2 classes on the same day
Classes cannot be over booked.
must make in some form or other something that displays how many classes taken and how many classes are left to each member.
I know all this stuff probably falls under some very simple functions that I'm just not using correctly or have no idea exist but I've made no progess in days and it's such a horrible feeling!
Please any help! I'm not asking you to complete my assignment I'm just asking you to shine the light my way!
ps, no code please, we havent got to that stage yet.
thanx in advance,
Joe
I have two queries combined into one query. The first query shows a sales summary by part number. No problem so far. The second query shows inventory by part number. No problem with that.
Now, I combine the queries, and get only those results that appear in BOTH queries. In other words, if there is no inventory, I do not get zero in the inventory column, the entire result for that part number is skipped.
How do I still display the results of the first query if there is no match in the second query?
Hi
I've happily been working with my new switchboard only to find it tells me I am limited to 8 entries. How does one get round this - create switchboards which link to switchboards, different pages of switchboard...I've got a bit lost and would much aprreciate any advice.
Thanks
Hey guys,
Was wondering, what is the recommended maximum size for Access databases? I've heard that for very large databases that oracle is preferred. Would Access be appropriate for something on the scope of 5000 records of roughly 6 text and 2 number fields? Do people ever notice performance effects on databases of that size?
Also are there any ways to help reduce the size of the database? Do you recommend change the default field sizes where appropriate and would this have a noticeable effect on a database of this size? Any other suggestions to help keep the database manageable, and functioning?
I'm basically trying to figure out if there's much to worry about here, before I start importing my data. Thanks.
I am trying to project how long it will take to reach the 2GB Access limit in a specific project.
My client downloads approx 250,000 records on a monthly basis. I've been provided w/ a sample Excel spreadsheet. I filled a spreadsheet w/ the max number of rows (65536 rows w/ 10 columns of data), and imported that into a new table in a new .mdb file. I copied the table 4 times, so I now have 4 tables of 65536 records each. Compacted before closing. Size was about 52MB. Assuming consistency, I calculate that the size limit would be reached in about 36 months.
262,000 records = 52 MB. 52 MB a month x 36 months = 1,872,000 MB.
Does my projection appear sound?
Thanks!
I have a textbox called Odours, there are four buttons below the textbox btn_Cadaver, btn_Drugs, btn_Explosives and btn_Money.
On licking one of the buttons a value will be put in he textbox i.e. pressing the btn_Cadaver will put the value "Cadaver ()" in the textbox.
The reason I want this is that all of the entries must begin with either Cadaver, Drugs, Explosives or Money. The brackets are there to allow the user to put some free text between the brackets.
I have some code to set the cursor (on click) between the brackets
Code:
Me.Breed.SelStart = 9
I want to prevent the user from typing anywhere in the textbox except between the brackets.
I will try and keep this brief. I have a paragraph of text, I have to paste it into a system which allows me 75 characters in a line and 208 in a page.
I have code which creates text boxes dynamically based on the total amount of characters/208 - this gives me how many textboxes need creating. I also have code which then populates those textboxes 208 characters at a time. I now need to alter it so it puts a line break every 75 characters.
Code:
Private Sub btnsubmit_Click()
DoCmd.OpenForm "NoteForm", acDesign
Dim x As Integer
Dim ctrl As Control
Dim y As Integer
[Code] ....
So I've achieved almost what I want, I just need to amend it so it starts a new line after every 75 characters...
The BIG problem is this.
I need a basic string graph over time recieved from a cross tab query. The data points all come in fine, but the "strings" / lines connecting the points do not show up.
I believe the reason is because there are just too many records.
I culled the data down using a vba loop to only show 1 stop per minute. (BTW, is there a way to do this using only SQL?) I now show all record points, but still do not show the lines between points. Some of the lines show up, but the more data points on the graph there are, the less the lines show up, till only one or two show.
This is an Access limitation that I'm unsure of how to get around. This really shouldn't be an issue though, because Excel handles the graph just fine EVEN BEFORE the culling of the data down to access acceptable limits. And it shows the lines between each record as well.
Does anyone know of a resolution for this?
Kelemit
Hi
I'm new to forum and can't find an answer to this problem. I am using Access 2002 and have a memo field in a form that I want people to be able to type in as much text as they want. This is then displayed in a report. The form accepts the text with no problems however when I view it in the report it has been cut to 255 characters as if it is a text box. I have set the properties to 'can grow' to no avail. I have searched the MS Knowledge base for a solution with no luck. I admit I'm not an expert in Access and would really appreciate some help as I have been trying to overcome this problem for days.
I have a form with an unbound textbox. I want to be able to enter a sequence of digits and spaces (e.g., 02 950 4187); however, when I paste this into the box, I always have to delete the two spaces until the number is 9 digits long. Then it will accept the number and go look for the prize. How do I get the textbox to accept the number I paste in?
View 5 Replies View RelatedHi,
I have a query that requires a Start-Date and an End-Date to be input by user for the Where clause. It is asking for both over and over. I've had it ask from 1 up to 4 times! :eek: Shouldn't it store the input and only ask for it once? I'm thinking that the way my query is arranged may be causing it to have to loop through that section more than once to find the data, but that's just my theory. Any help would be great!
Here is my code (abbreviated slightly):
SELECT DISTINCTROW C1.*, C2.*
FROM Pen AS C1 INNER JOIN Jobs AS C2 ON C1.subno=C2.[Jobs Acct]
WHERE ((C1.typ="SS" Or C1.typ="CC" Or C1.typ="PP" Or C1.typ="TT") And C1.stdate>=[Enter Start Date] And C1.stdate<=[Enter End Date] And C2.[Type]<>"EE" And C2.[Type]<>"QQ" And C1.entdate<=C2.[ChangeDate]+60);
I'm selecting rows from "Pen" and "Jobs" that have the same subno/Jobs Acct numbers (text), then there are criteria for "Pen" types, user inputs criteria for date range (Start Date and End Date) and there are criteria for "Jobs" types. Finally, there's a cross-table criteria based on a date field ("Pen" entdate should not be more than 60 days past the "Jobs" ChangeDate). Tables are in quotes in my explanation here.
So running the above, it asks for user input "Enter Start Date", then again for "Enter End Date"...but then it asks for each again...and again...and sometimes again!
Help! :confused:
P.S..I didn't notice this repeating until I made it user input (parameter query) because it was using whatever dates I hard-coded in there before.
I wanted to assign the field "Number of magazine" with special format based on date/time format but showing only year and month in the format: "yyyy-mm".
So in property of this field in format I put yyyy-mm and in input mask I type 0000-00;;-
I also created the form based on the table containing above field and I defined format and input mask for corresponding formant in the same way like at the table.
But if I try to type date for example 2014-01 in text box of the form it comes up with the full date 2014-01-01. Why does it do like this? What do I do incorrectly?
Hi All,
How can I check the Parameter query interval is out of range?
Details:
I have a table with Date column. With parameter query(by Date field) I extract records between two dates. How could I make a check when taping in Inputboxes, am I or Not Between the Last and the First Date?
Thanks!
I'm writing a query which when run will prompt the user to enter criteria. In this case it is a workers id (like 000UA or 000UB)
My problem is I would like to have the ability to enter more than one criteria. What I want to do is to have the ability to enter several worker id's in the criteria field, like 000UA, 000UB, 000UC, 000UD, 000VA, 000VB, 000VE, 000VF, 000WA, 00WB
Is this possible and can someone explain to me how to do it?
Thanks
I am trying to run a parameter query that will prompt the user for "facility", but instead of displaying the results in table format, I want a form to display the results. I have already developed the form. I would also rather for the users to search by a list box instead of typing the facility. I cannot seem to figure this out. Please help.
Thanks.
Dear All,
I am having problems finding a solution to this problem. I am working on 3 databases at the moment..and am at the final hurdle to complete it.
I have built an Access database. I would like to generate a
number of reports which have to be exported directly into excel
individually.
I have a parameter query with 14 columns. One of the columns
is called GROUPS. I have a form with a button.
When you click on the button the parameter query asks for the
parameter value.
The parameter value must be a GROUP. i.e BURR, WIEN.
After you enter the group the parameter query generates results for
that particular group.
I have a number of groups which i would like to generate results
for at the click of the button on the form.
There are around 30 groups. but i only need to generate reports for
about 15 groups. The groups are stored in one of the tables.
So for instance after i click a button on the form
the results for each requested "GROUP" are exported individually into
excel.
Each group result must be in one excel file.
How can i achieved this. Do i need to use VBA? Please can you help.
I need a solution then i can apply this to a number of databases
Many, Many thanks
I want my msgbox to tell my user that this projectId does not exist in our db if the sql search returns an empty value. I get errors that i can't run this action query. please see red text. any ideas?
Private Sub Project_Quick_Find_Click()
On Error GoTo Err_Project_Quick_Find_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim Ssql As String
Ssql = "Select [projectInformation].[projectId] from [projectInformation]" & _
"where [projectInformation].[projectId] = " & Me![ProjId]
'DoCmd.SetWarnings False
DoCmd.RunSQL Ssql
'DoCmd.SetWarnings True
stDocName = "Project Status - Full Details"
stLinkCriteria = "[projectId]=" & Me![ProjId]
If Ssql = "" Then
MsgBox "A Project with this number does not exist in our database", vbExclamation, "Cannot find project"
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Exit_Project_Quick_Find_Click:
Exit Sub
Err_Project_Quick_Find_Click:
If Err.Number = 3075 Then
MsgBox " Please enter a Project ID to find! ", vbExclamation, "Empty Field"
Else
MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"
Resume Exit_Project_Quick_Find_Click
End If
End Sub