Hi All,
This question has probably been asked before, but couldn't find a related topic. My question:
How can I import long numeric values (integers) greater than 2,147 billion (standard long integer). I tried a lot, but couldn't find the answer.
Thanks.
Hi.
I have a form with two text boxes, call them FIRSTNO and LASTNO.
The user inputs a first and last number into these boxes, and what I want to do is append to table NOS all integers between these two.
E.g. user inputs 6 and 11 and I would like table NOS to contain 6 rows, with the first field containing this number, like so:
6
7
8
9
10
11
It sounds easy, but I’m struggling!
Any thoughts appreciated. Thanks in advance.
I need to exclude non-integers in my query and have forgotten how to do this.
What i need to put into the criteria field within my query to to this?
I currently have a calculated field that will result in either a whole number or a decimal. From there, I need to set up a field that identifies if the previous field is either a whole number or a decimal.I have tried several different formulas, but always seem to get an error or a query that does not return any results.
View 3 Replies View RelatedI have a text field having data i-e HO-1, HO,2, ACW-25 and so on. The field name is nBadge_num and is Unique. The data in this field is sorted automatically like 1, 10, 11, 12, 13, 2, 3, 4, 5...because this is the text field.
The number on the form is automatically generated, when the user type HO- for example on field exit event. The last number will generate like HO-5.
Code for automatic number generation is:
Dim dbs As Database, rst As Recordset, Response
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Max(Right([nBadge_Num],Len([nBadge_Num])-" & Len(Me.NBadge_Num) & ")) AS MaxNo " _
[Code]....
My problem is when the number is generated it give HO-5 instead of HO-14, How can I sort the numeric part of the field ?
I need to be able to precisely handle nonnegative integers (in the math sense of integer, not the VBA sense, of course) between the values of 0 and 808,035,046,350,570,000. (Don't ask.)
I'm wondering whether I should be handling these numbers in a string and do everything manually, or whether the datatype Decimal or Variant will work for me.
I read that Decimal handles +-79,228,162,514,264,337,593,543,950,335 with no decimal point, or +-7.9228162514264337593543950335 with 28 places to the right of the decimal.
I absolutely cannot have the "rounding type errors" and "oddities" that you get by storing integer values in doubles, for example, due to imprecission.
Seeing that Decimal seems to work in a decimal point manner worries me, that perhaps I shouldn't be using it for this scenario. However, it's range seems so much larger than what I need, I'm wondering if I would "get away" with it.
If needing no imprecission to creep in means that I have to use Strings and implement my own String math library, that's the route I would have to go.
Question 1 - If I need the numbers to be exact (again, nonnegative integers, no decimal points), do I need to use a String, or should I use a Decimal or Variant type?
Question 2 - What is the difference between Double and Variant, for the purposes of storing numeric values? I read that Doubles are 8 bytes, and Variants are 16 bytes, however the Variant description is "Any numeric value up to the range of Double", which confuses me since it's double the size.
I have a query which returns, among other things, a number of boolean fields. In some cases, there will be a genuine True or False value in each of these fields; in others, it can and should be Null (e.g. as a result of a 'failed' LEFT JOIN of some description, where there is no associated record in the joined table which fulfills the criteria)
So something like this :
Code:
SELECT [tblTable2].[fldBooleanField]....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]
However, I will be writing the result of the query to a text file and here's the problem. I want to show a numeric value for a genuine True / False (i.e. -1 and 0 respectively using the standard boolean conversions in Access) and a blank for any Null values.
So I tried this :
Code:
SELECT CInt([tblTable2].[fldBooleanField]) AS fldBooleanField....
FROM [tblTable1]
LEFT JOIN [tblTable2]
ON [tblTable1].[SomeID] = [tblTable2].[SomeID]
However, currently when I look at the exported recordset in Notepad, I am getting 0's for both False and Null values (and -1 for True)
How I can adapt my query to keep Nulls...null? And convert the genuinely present boolean values to integer form?
Only thing I can think of is to use (untested) :
Code:
IIf([tblTable2].[fldBooleanField] Is Null, Null, CInt([tblTable2].[fldBooleanField]))
But there's a number of boolean fields in there, all requiring the same treatment.
I need to know how big a number I can hold in a "Long Integer". The only documentation I can find says it's roughly +/- 2 billion.
Is that a USA billion (10^9)or a UK billion (10^12) ?
I'm guessing it's a 32bit number so that would be a USA billion. Can anyone confirm this ?
Here is my sql statement that I want to get the total number of "yes" in each field. Each field is a text field.
SELECT Sum(Homeless="yes")*-1 AS HomelessCnt, Sum(NFresident="yes")*-1 AS NFresidentCnt, Sum(Self="yes")*-1 AS SelfCnt, Sum(StaffBoard="yes")*-1 AS StaffBoardCnt, Sum(FamilyFriend="yes")*-1 AS FamilyFriendCnt, Sum(CommLarge="yes")*-1 AS CommLargeCnt, Sum(ServeProvide)*-1 AS ServeProvideCnt, Sum(Physical="yes")*-1 AS PhysicalCnt, Sum(MentalEmo="yes")*-1 AS MentalEmoCnt, Sum(Cognitive="yes")*-1 AS CognitiveCnt, Sum(Vision="yes")*-1 AS VisionCnt, Sum(Hearing="yes")*-1 AS HearingCnt, Sum(Multiple="yes")*-1 AS MultipleCnt, Sum(AdvocacyServe="yes")*-1 AS AdvocacyServeCnt, Sum(AssistDevServe="yes")*-1 AS AssistDevServeCnt, Sum(ChildServe="yes")*-1 AS ChildServeCnt, Sum(CommServ="yes")*-1 AS CommServCnt, Sum(FamilyServe="yes")*-1 AS FamilyServeCnt, Sum(HousingServe="yes")*-1 AS HousingServeCnt, Sum(ILSkillServe="yes")*-1 AS ILSkillServeCnt, Sum(InformationServe="yes")*-1 AS InformationServeCnt, Sum(MentalRest="yes")*-1 AS MentalRestCnt, Sum(MobilityServe="yes")*-1 AS MobilityServeCnt, Sum(PeerServe="yes")*-1 AS PeerServeCnt, Sum(PersonalServe="yes")*-1 AS PersonalServeCnt, Sum(PhysicalRest="yes")*-1 AS PhysicalRestCnt, Sum(PreventiveServe="yes")*-1 AS PreventiveServeCnt, Sum(Prostheses="yes")*-1 AS ProsthesesCnt, Sum(RecServe="yes")*-1 AS RecServeCnt, Sum(RehabTech="yes")*-1 AS RehabTechCnt, Sum(CounselServe="yes")*-1 AS CounselServeCnt, Sum(Therapeutic="yes")*-1 AS TherapeuticCnt, Sum(TransportServe="yes")*-1 AS TransportServeCnt, Sum(YouthServe="yes")*-1 AS YouthServeCnt, Sum(VocationServe="yes")*-1 AS VocationServeCnt, Sum(OtherServe="yes")*-1 AS OtherServeCnt, Sum(Newsletter="yes")*-1 AS NewsletterCnt, Sum(EventFlyer="yes")*-1 AS EventFlyerCnt, Sum(VFIBrochure="yes")*-1 AS VFIBrochureCnt, Sum(WaiverInfo="yes")*-1 AS WaiverInfoCnt, Sum(Survey="yes")*-1 AS SurveyCnt, Sum(Other="yes")*-1 AS OtherCnt, Sum(Advocacy="yes")*-1 AS AdvocacyCnt, Sum(SkillTraining="yes")*-1 AS SkillTrainingCnt, Sum(PeerSupport="yes")*-1 AS PeerSupportCnt, Sum(Deinstitutionalization="yes")*-1 AS DeinstitutionaliztionCnt, Sum(ReferralPAS="yes")*-1 AS ReferralPASCnt, Sum(ReferralVFI="yes")*-1 AS ReferralVFICnt, Avg(MinSpent) AS MinSpentAvg, Avg(CallerAge) AS CallerAgeAvg
FROM PersonalInfo
WHERE (((PersonalInfo.CallerCounty)=[Forms]![CityStateCountyForm]![CmbCallerCounty]) AND ((PersonalInfo.CallerCity)=[Forms]![CityStateCountyForm]![CmbCallerCity]) AND ((PersonalInfo.CallerState)=[Forms]![CityStateCountyForm]![CmbCallerState]));
It says:
"This expression is type incorrectly, or it is too complex to be evalutated. Try Simplifying the expression by assigning parts of the expression to variables."
Does this mean I can not do it all in one query, or how do I assign variables. Sorry I am kinda new to access. Thanks for the help.
Bobby
Consider the following...
Select id, A & “ “ & B as Res
From tblMain
A is type text
B is type memo
Result cuts cut off at 255 Characters….
How can I get the entire result – More than 255 characters??
Thanks URL
I have an autonumber field set up as long integer. The field just reached the value of 32670 and I get the overflow message. I thought a long integer
could be much bigger than that before running into that problem.
I got around it by re-creating the field and starting from 1, but would rather
know why it's doing it so I don't have users without their system.
Thanks in advance for any help.
I would like to have a back-end Database on a host server and link a
front-end Database to the tables several hundred miles away. The problem is I have no experience whatsoever in doing this.
My questions are:
(1) Can tables be linked via the Internet?
(2) If they can, is this the best way to link the tables?
(3) If it is possible to link the tables by the internet or by some other way; how do I do it?
I really would appreciate any help you can give.
Kind regards
Eddie
I am using Access 2007 and trying to set the BackColor property of a text
box on a form using VBA Code in the Form_Open event with code like this:
Me.txtName.BackColor = <some global variable>
Here's the thing: In order to get the correct color numer, I first pick my
desired color from the palette to see its value. In this case, a very bright
red comes up as #ED1C24.
In Access 2003 and earlier, this value would be represented as a long
integer, not a Hex. So, my question is how to get the HEX value converted to a long integer??
The BackColor property is supposed to be a long integer data type, but that is NOT what the design interface is presenting to me!
Why did Microsoft change the BackColor property of form and report objects from long integer representation to Hex?
CONFUSED!!
I'm sorry if I'm asking relatively simple questions. I'm a break/fix guy, and our in house programmers told us it would be 6 months before they could help us out, so we're tackling this chore ourselves.
We have a database of company names along with corresponding contact information. Each company has at least one associated id number. One company may have several numbers, but each number is unique. A very simple creature. I'm using a combo down box to allow the users to search by company name. This box only pulls the distinct company names. There is also a second combo box that has every id number. These are the only two methods used to navigate through the form.
How can I set up these two boxes to "track" each other? Right now, if I select a company name, the id number box doesn't move to its corresponding entry, and vice versca. How can I set these two boxes up to follow one another as a user navigates?
Our second problem might be a little more challenging. Like I said, each company may have more than one id number. We would like to set up a query that would pull all of the company id numbers when a user selects a name using the above drop down box and insert them into a text box.
Any help is appreciated.
Thanks!
Hello all!
I find it difficult to build a query...
The scenerario is the following:
I have a table that keeps information for weeks about some files (e.g: if the file has been sent). Then I have another table with information for days in a month (the data for every day).
What I should do is:
Check wheter the week has been sent, if it has been sent, I have to show the data for every day of that week in a report. Only for one month... and I can't figure out how to build that query...
I guess I should do something like:
select week from weekTable where week=week and sent=TRUE
set rcset=db.openrecordset(sql)
if(not rcset.eof)then
'build in here the query to select the data I need from the month table
end if
but i can't figure out how the loop would be for 1 month...
Any help will be much appreciated!
I have an Access form linked to two SQL tables, I had to make a change to the field length/datatype of a field named notes. The form now tells me that the "text is too long to be edited" whenever I try to add data to that Notes field in my form. I went from char to nvarchar... how do I fix this?
View 3 Replies View RelatedHi,
i'm not sure if this should be posted under queries or forms...
i have a series of combo boxes in a from based on a query.
each combo box needs to display options for characteristics of each section of a motor (ie. combo box for 'Corroded' = 'slightly', 'very', 'not at all', combo box for 'dents' = 'none', 'top', etc)
my table structure works like this: i have a parent defintion tbl that defines the list of combo boxes (i.e. 'corroded', 'dent', 'rub marks') and assigns then and ParentID number that links to the tbl:InfoObjectDefinitions that defines ALL the possible characteristics ('slightly', 'very', 'not at all','none', 'top') and assigns them an ObectID number.
So in a nutshell, i can pull the possible numeric options (OID) and text values('slightly') for 'corroded' from the table InfoObjectDefinitions according to 'corroded's PID.
I then store the OID in the column for 'corroded' in tbl:Repairs.
so i need to create a form that lets people add/edit entries in the repair tbl using the text values and not the id numbers.
i can create a query for the form, that joins the repairs table and the InfoObjectDefinitions table, and substitutes the numeric values from the repairs table with the text values in InfoObjectDefinitions but then the comboboxes arent editable???? (the combobox expands, and i can see all the text values but cant select a new value)
The SQL that created my query 'repairFormTest' is:
SELECT Repairs.FENumber, Repairs.Corroded, (select otext from InfoObjectDefinitions where pid = 17 and oid = Int(Corroded)) AS CorrodedTxt
FROM Repairs;
and the form places the field CorrodedTxt as the Control Source of the combo box with 'select otext,oid from infoobjectdefinitions where pid = 17' as the Row Source.
And as i mentioned, the box lokks right, and expands correctly but i can select another value.
Any help appreciated, i really stuck.....
Thanks,
Vauneen
I have written a bit of code in a Microsoft Access 97. The code basically creates a Microsoft outlook message. I am trying to add in the message that I want to display in Outlook by using the MyMessage ="" command. But the message I want to add is too long to fit on one line of code - I get to the end of the line and can no longer type any more! I have tried to add the second part of the message to the next line but all I get is an "Expected line number or label or statement or end of statement" error message. I think this might be because access keeps putting a " at the end of the first line - every time I remove it and add the " to the end of the second line it keeps adding it back to the end of the first line!
any ideas would be gratefully received!!!!!
I'm having problems Using Dcount function, when I use it with a text field like the following it works fine: (but using a Surname as a criteria can have problems.... I've people with the same surname in my database...)
times = DCount("[Surname]", "Booktoscore", "[Surname] = Forms!Teachers!Surname.value")
But, If I try to use it with a number, then it doesn't work, the problem seems to be with the criteria.... Because Access don't show me any msgbox with errors....
times = DCount("[IdCandidate]", "Booktoscore", "[IdCandidate] = Forms!Teachers!IdCandidate.Value")
Hi all,
I have a table that has a long date format for example 05/05/05 02:40:34 AM how can I add an 8 hours to the date and time. Please help if you can because I"m going nuts!!! thanks. I want it to look like 05/05/05 10:40:34 AM
Hi there,
I have on MS Access database.which was working fine untill now but suddenly it started this strange behaviour.
When i open it it opens without any error but stays in the frozen state for like 5 mins means i cant do any thing or i cant click anywhere.
and after 5 mins it starts working fine.Does anyone have any clue abt this strange behaviour.
Thanks
Danny
Hi all,
here's me again,
I have a question, I'm trying to generate an autonumber but of 4 digits, for example
the first one is: 0001 and autonumber 100 is: 0100, and so on
how can I do that?
any help will e very much appreciated!
Best Regards
CS.
I'm hopeless with input masks. Can anyone help?
I want the user to only decide the first two digits in a 24 hour clock.The minutes should be 00 and no seconds, e.g.
12:00
21:00
etc.
Let's say I have a query like:
select [x]+[y] as A, [z]*[A] as B from MyTable.
I have found that sometimes this works and sometimes it doesn't, (Like, it works the first time I run it, but later quits working....)so I've gotten into the habit of repeating my calculations and writing it thus instead:
select [x]+[y] as A, [z]*([x]+[y]) as B from MyTable.
However, I now have some query fields that have so many calculations in them that I exceed the string length that Access allows for a query field. So is there a way to make the shorter SQL work consistently?
Any ideas appreciated.
Thanks!
PS: Here's the field I need to shorten:
AdjFcstDay: IIf(((Sum([AvgDaily])*First([forecastweeks])*5+nz(First([backlog]),0))/(First([forecastweeks])*5))+IIf([wiptotal]<(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev])))-[wiptotal])/5,IIf([wiptotal]>(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev])))-[wiptotal])/5,0))<0,0,(Sum([AvgDaily])*First([forecastweeks])*5+nz(First([backlog]),0))+IIf([wiptotal]<(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([lowwipflag]))*First([dailystddev])))-[wiptotal])/5,IIf([wiptotal]>(IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev]))),((IIf(First(CDbl(nz([dailystddev],0)))<=0,0,xlnormsinv(First([highwipflag]))*First([dailystddev])))-[wiptotal])/5,0)))
Hi
I was wondering if anyone can help me.
I am trying to build a database as part of my IT A-Level coursework, but I have little experience with Access. Although I have built the main part of the database, I am now stuck with something which I am sure is quite simple, if only I knew how to do it!!
I have got four tables at the moment, Suppliers, Category's, Products and Orders. From these I have made a form which will be used to put in new orders, and it works so that when it is filled in, the "order" is automatically entered into the Orders table.
My problem is that in this form I have drop down boxes for category name, supplier name and product name, which are looked up and taken from the respective tables. I want to have it so that when I select a category from the category drop down list, it automatically limits my choices in the suppliers list, and offers me only the suppliers which supply products in the category I have chosen. Then the products list should only be the products from the selected supplier.
I apologise if that makes absolutely no sense at all, it is very difficult to explain by writing it down... my teacher can not help me, she appears to know even less about databases than me, and I have had to teach myself the very little I know! I know this must sound very pathetic and trivial, but I just can not make it work! Any help would be greatly appreciated!
I also have another slight problem in that I can get my form to calculate the total cost of the order, by taking the price and the quantity, but then this value does not appear in my orders table.
Thanks for taking the time to read this and helping me if you can!! I am really grateful!
Charlotte xx
I'm writing some "Help" for an Access database that I've created, but lots of others will be using.
All I can think of is to type it all into a form which is then displayed when the user clicks a button I'll create on the menu. As it's going to be a long form, I want to put a "Contents" section at the top, with hyperlinks to relevant sections further down.
Is there a way to make one label (cos that's all the text is) hyperlink to another label in the same form? Or should I be going about this in a completely different way?
I've read a lot of the hyperlinks Q&As in the forums, but haven't found the answer to this yet. Thnaks in advance for any help.