Using DCount With Long Integer?

Dec 5, 2011

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")

View Replies


ADVERTISEMENT

Long Integer Overflow

Aug 11, 2005

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.

View 4 Replies View Related

Tables :: Performance Analyzer - Change Data Type To Long Integer

Dec 22, 2012

When I run the analyzer on all object types it recommends to change the data type for field "zip" (zip code) to "long integer to:

"benefit that table and potentially other objects in my database"

The field type is currently set to text, And I have the same setting for the same field in a separate table, yet it does not come up with a recommendation for that table.

Additionally, I don't seem to have the option "long integer" for the field data type???

jeds - using Access 2010

View 5 Replies View Related

Queries :: Access 2007 - Update A Field In A Table With A Random Number / Long Integer

May 27, 2014

Is it possible to run a SQL command to update a field within a table with random numbers?

More specifically - random long integers linking back to an ID (autonumber) field in another table?

Background to this is, I have multiple static data tables related to each other by long integer identifiers (autonumbers)

The structure is fine but I haven't been provided with the actual data yet - but for development purposes, I need to work on other functionality which requires that this data be present.

So I want to fill my table with dummy data such that I can go off and work on the remaining functionality, but then just go back and clear it all out once I get the actual data.

I have one 'main' static table, which links back to other tables, which I have already populated with dummy static (i.e. company names, locations etc) Now I want to go into my main table and populate those fields in each record with a random ID. I don't mind doing this field-by-field (there's only a handful) but I've a lot of records in there (~1000) so I'd rather not do this record-by-record.

View 6 Replies View Related

Check If Integer

Oct 11, 2005

What the easiest way to turn text to an integer - ir have vaiable defined as long and an inputbox - want to keep asking for an input until I get an integer.

Have IsNumeric - can this be applied to int.

Or could I use localised error handling?

Thanks

View 9 Replies View Related

Year As Integer

Jul 13, 2005

There's a lot of info that I need to keep track of by just the year. If I enter it as a normal date, I would need to extract the year every time I need to query and then do what ever. Would it be easier just to extract it once, convert to an integer and use it like that through out the system when I need to query by year?

Thanks,

scratch

View 5 Replies View Related

Integer Length

Aug 30, 2006

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 Related

Changing An Integer To A Month

Oct 18, 2006

I have a form that asks what month you completed a file (04, is april ect.). From this form a report is opened, in my report, I have it read this number and I want to display the month for the number thats entered (if they enter 04 I want april displayed on the report). There is already a ton of data that has the MonthCompleted as 04, so changing that is out of the question.

Thanks, JOe K.

View 3 Replies View Related

Help - SQL Lookups (Text Or Integer)

Apr 27, 2006

Hi Guys,

First off, a big thanks to everyone on the site. I have learnt a lot since first discovering this site a few weeks back.

Problem:

Having understood that it is better to create SQL lookups to queries of tables rather than to the tables directly, I am having trouble understanding what value I should store in my main table, a text value or the ID (number) (of the text value.)

I have an asset table with a field Equipment Type. This field looks at a query of the EquipmentType Table.

Would it better to store the text value "Printer" in the main asset table (in which case I can query the table directly but the field will use up more space (i.e. 25 char)?)

or

Store the Equipment_Type_ID "1" relating to the Printer (will use up less space, but mean any queries querying the actual name would have to include the EquipmentType table).

Any advise would be much appreciated.

:confused: My thought was that I should go with the ID as otherwise I will be storing duplicate data. If this is the case, when would a text value be more suitable.

View 1 Replies View Related

Round Integer Division .5 To 1

Nov 8, 2007

Please help me with the round function. I want .5 to round to 1.
Here is an example of my data: (18+18+18+20)/4 = 18.5 rounds to 18. I want it to round to 19.

I used the following expression:
RoundACT Composite Score: Round((([Column1]+[Column2]+[Column3]+[Column4])/4),0)

Thank you.

View 6 Replies View Related

Convert String To Integer

Dec 4, 2007

Hi,

I have a field in my database which captures either single digit numbers or comments in text format. I want to be able to count the numbers but obviously I've had to use a memo field in order to capture both numbers and text.

The only way I can think of is to take the field and look for single character responses, then convert these into a number field so that it can be counted.

Does anyone have any idea how to do this?

Thanks.

View 3 Replies View Related

BeforeUpdate(Cancel As Integer) - Question

Aug 2, 2007

I'm trying to get the BeforeUpdate(Cancel As Integer) to work for me.

I have got it to work with Me.Date2 = Now()

But I do not want the Date and Time, just the date.

If I try Me.Date2 = Date

Nothing seems to work.

Any ideas, I do not know if there is another command that I can use or not.

View 10 Replies View Related

How To Send Count(*) --> Declared Integer

Mar 14, 2006

SQL Query
SELECT COUNT(*)
FROM Orders
WHERE (((Orders.Date) = [forms]![frmOrders]![DTPdicker2]))
VBA CODE

Option Compare Database
Dim iDay As Long
Dim iCount As Long

Private Sub DTPicker2_Change()
iDay = 0
iCount = 0
iDay = Weekday(Me.DTPicker2.Value)
iDay = iDay * 100
'iCount = Count(*) from SQL query above'
iDay = iDay + iCount
Me.txtRefNumber = iDay
End Sub


Basically I am trying to generate a meaningful reference number while adding new orders rather than using an ever growing unique id(AutoNumber), for creating Weekly Reports.

I have tried using the following code
iCount = DoCmd.RunSQL (qryReturnCount)
and I get an error

Any help would be greatly appreciated.
Last question for the gurus out their can it be done with the expression builder? To save me banging my head against VBA syntax.:mad:

Cheers Andy!!!

View 2 Replies View Related

Using An Integer From A Subform To Filter A Report.

Aug 17, 2006

THis has to be an easy issue.

I have a subform that in the on curren event i passes the ClassId out to my main for in an unbound text box

Forms![Student]![ClassID] = Me![ClassID]

I know this part works

I then have a command button that should pass ClassID to a report so that it can be filtered. Here is the on_click code

Private Sub cmdReprintAccom_Click()
On Error GoTo Err_cmdReprintAccom_Click

Dim stDocName As String
Dim strReptCriteria As String

strReptCriteria = ClassID
stDocName = "Forms - Accomodations"
DoCmd.OpenReport stDocName, acViewPreview, , _
"[ClassID] = '" & strReptCriteria & "'"

My problem is that I keep getting a type mismatch error. I know that it is because CLassID is a number and it is getting passed as a string i just can't figure out the syntax to the highlighted code.

View 5 Replies View Related

Converting Integer To Time Format

Sep 5, 2007

Is there an easy way to convert an integer into time? What I want is for the person to enter in 0820 or even 820a and then convert it to 8:20 am. This is mainly to save keystrokes.

View 5 Replies View Related

Modules & VBA :: Changing From Integer To Text

Nov 23, 2014

I am having a query which having a category field like Electrical, Sports, House hold etc.

What I want that if i select Electrical then it should return 15, if Sports then 10 and so on i think this could be done through this below mentioned VBA but it need change from integer to text...

Option Compare Database

Public Function fncGrade(intNum%) As String
Select Case intNum
Case 0 To 1: fncGrade = "Same as Previous"
Case 2 To 32: fncGrade = "C-3"
Case 33 To 40: fncGrade = "C-2"
Case 41 To 50: fncGrade = "C-1"
Case 51 To 60: fncGrade = "B-3"
Case 61 To 70: fncGrade = "B-2"
Case 71 To 80: fncGrade = "B-1"
Case 81 To 90: fncGrade = "A-2"
Case 91 To 100: fncGrade = "A-1"
Case Else:: fncGrade = "X-X"
End Select
End Function

View 5 Replies View Related

How To Cast A String Into Integer Or A Real Number?

May 8, 2007

Iff(fico>600,1,0) as g,
I found fico is a string in access table. so the above does not work.
How to fix this problem
Thank you very much.

View 3 Replies View Related

Converting Two Date/time Fields To One Integer

Sep 15, 2006

Hi,

I'm combining two date/time fields in a query to an integer. The first field has the date, the second the time. I'd like the resulting integer to be without the opening 0. How can I do that?

That is, these are the two fields:
2006-09-14 (date/time)
15:00:20 (date/time)

And I'd like those two combined to be 60914150020 (integer) in the third field in the query.

Grateful for advice!

View 3 Replies View Related

Modules & VBA :: Return Integer Based On Time?

Oct 30, 2013

I have a query with a Start Time where the need to return a set integer in another field in my query. I am attempting to get this to work in my StripSecondsQry.

I am not getting any error messages and I am not getting any output, When I view this in the Locals window I can see that it should be returning 7, but instead I get nothing unless I change it to

Code:
Function SortStart(StartTime As String) As Integer

then I get zero.

I had this working within the query, but I had to add one more time and then received a message that the expression was too complex.

Code:
Sort_Start: IIf([StartTime]="7:00 AM",1,IIf([StartTime]="8:00 AM",2,IIf([StartTime]="8:45 AM",3,IIf([StartTime]="9:00 AM",4,IIf([StartTime]="9:15 AM",5,IIf([StartTime]="10:00 AM",6,IIf([StartTime]="10:15 AM",7,IIf([StartTime]="10:30 AM",8,IIf([StartTime]="12:00 PM",9,IIf([StartTime]="1:30 PM",10,IIf([StartTime]="1:45 PM",11,IIf([StartTime]="2:00 PM",12,IIf([StartTime]="3:00 PM",13,IIf([StartTime]="4:00 PM",14))))))))))))))

View 3 Replies View Related

Adding Decimal Place To Integer Field

Jun 9, 2015

Successful in loading an Access database with data from the PLC's memory. Unfortunately, I'm only able to transfer Integer values. In the database I'd like to show one of the fields as a real number by formatting the Integer value to add a decimal place. Example: 2505 to 250.5

View 11 Replies View Related

Forms :: Calculate Integer Difference Between Due And Result Date

Sep 20, 2014

Trying to calculate the integer difference between Due_Date and Result_Date excluding weekends and holidays.

I have a table (Holidays) with the dates of the holidays in it. The table looks like:

ID Description Holiday
1 New Years 1/1/2014
2 New Years 1/1/2015

So, if Date_Due: 9/25/2014 and Result_Date: 9/29/2014, then TAT = 2

Since 9/27/2014 and 9/28/2014 are weekends they are excluded from the calculation and only that Thursday and Friday are used in the calculation.

Code:
Private Sub Result_Date_AfterUpdate()
[TAT] = NETWORKDAYS(Due_Date, Result_Date, tblHolidays)
End Sub

View 7 Replies View Related

Modules & VBA :: SQL CAST Function - Return Integer Value Of String

Aug 28, 2013

Need to use CAST to return integer value of string (digits as data type string).

Where clause looks like this:

... Where Cast([Price File] as int) > 0

works fine in SQL Server but not sure what syntax is in VBA . Using Paul Baldy's suggestion to set Select statement as string and do the debug.print to verify that SQL has no goofs ... looks good but not to Access. What is proper syntax?

View 12 Replies View Related

Modules & VBA :: Count Total Number Of Integer In A List

Oct 7, 2013

In one table, I have a few fields. One of the field is "ItemSequence" and another one is "TotalPcs"."ItemSequence" is where user key in the sequence number for one or more item. 5 example for possible content of "ItemSequence" is as following :

1) 7
2) 4,6,9
3) 5-9
4) 3,5,9, 23-25
5) 3-5, 8-10

"TotalPcs" is the total number of items key in to "ItemSequence". For the 5 example above, the related "TotalPcs" should be as following:

1) 1 (1 item, which is item 7 alone)
2) 3 (3 item which is item 4, 6 and 9)
3) 5 (5 item which is item 5, 6, 7, 8 and 9)
4) 6 (6 item which is item 3, 5, 9, 23, 24 and 25 )
5) 6 (6 item, which is item 3, 4, 5, 8, 9 and 10)

For time being, the user have to count manually to get the "TotalPcs". I wonder is there a way to calculate the "TotalPcs" by programming?

View 7 Replies View Related

Modules & VBA :: Calculate Integer Number Difference From Two Dates

Sep 20, 2014

Basically, I am trying to calculate a integer number difference from two dates (TAT = Due-Date - Result_Date). The number is calculated and excludes weekends and ideally holidays (for that I have a tblHoliday but not sure how to use it). The function below seems to calculate a number but doesn't exclude weekends.

For example, if Due_Date is 9/26/2014 and Result_Date is 9/30/2014, then TAT is calculated to be 5 (should be 2).Since 9/26/2014 is a Friday only Friday and Monday are used in the calculation.

Code:

Option Compare Database
Public Function WorkingDays(StartDate As Date, EndDate As Date) As Long
Dim intCount As Long
intCount = 0

[code]...

View 1 Replies View Related

Converting Text Dates To Integer With Nulls Present?

Mar 18, 2013

I have a text field like, 11242010, and I need to be able to convert it into 3 int fields, day, month, year. I am trying to do this in a query and have create the following three;

DateD: IIf([DATE] Is Not Null,(CInt(Left(Right([DATE],6),2))))
DateM: IIf([DATE] Is Not Null,(CInt(Left([DATE],2))))
DateY: IIf([DATE] Is Not Null,(CInt(Right([DATE],4))))
Time: IIf([TIME] Is Not Null,[TIME])

When I have a value of Null, i keep getting #Error, I think when it's null.

View 7 Replies View Related

Using Switch Statement In Access Query - Output Integer?

Mar 27, 2013

I am trying to use a switch statement in a access query and i be leave i have the code right but for some reason it will only output a string i need it to output an long integer . (All the columns that are in the statement are Numbers).

Code:
columnName:Switch (([column1]<>0), [column1], ([column2]<>0), [column2], ([column3]<>0), [column3],([column4]<>0),[column4],([column4]=0),0)

Outputs a string i need an long integer

I have tried

Code:
columnName:Switch (([column1]<>0), CLng(column1), ([column2]<>0), CLng(column2), ([column3]<>0), CLng(column3),([column4]<>0),CLng(column4),([column4]=0),0)

and does not work just outputs a string. It outputs the right number but not as an integer...

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved