Simple T-SQL Problem Adding A 0 With A Field

Mar 6, 2008

Hello,

I am having a small problem inserting a 0 in a text field that contains a date. I work with large amounts of data derived from other systems and I have to format them to meet the requirements to be feed into others. I need a query that will only insert a 0 to the single digit day within a date where a text field date looks like say 01/2/1998 and I need it to look like 01/02/1998.

Thanks in advance for any help!!!:

View Replies


ADVERTISEMENT

Adding A Simple Text Clock To A Form

Apr 19, 2005

Trying to make a simple clock inside my form.
So far I got my clock but it isn't running.
It only get's the time when I open the form but I want it to keep track of time.

Searched the forums but couldn't find anything that relates to this problem.

View 9 Replies View Related

More Than One Unique Field - Simple One.

Oct 18, 2005

Wotcha, folks.

I'm in a right pickle at work at the moment.

For the life of me I can't make an Access table have more than 1 unique field.

For example, I want a company's name and postcode to be treated as unique ONLY when they appear together.

So I can have many companies with the same name and different postcodes, but no doubles of the same company name and same postcode.

Does this make sense? How do I trigger a unique property when two certain fields match a previous entry.

I've searched on the net and in these forums, but i can't find help. Maybe it's the way I'm wording it.

So, sorry if this is incredibly straight forward, and I'm being a noob, but this is rather urgent.

Many thanks.

Jake.

View 5 Replies View Related

Required Field... Simple

Jun 23, 2005

I can not for the life of me figure out how to make one field required. The name of the field on the form is Selectee. Can someone help?

View 2 Replies View Related

Simple Currency Field Comparison

Nov 22, 2005

Simple, I'm sure, but I am having one of those days...

I just need to compare two currency fields and do something if the difference is greater than one cent.

Any help with that incredibly simple expression, please? :D

View 2 Replies View Related

Field Lookup Function (simple Probably)

Mar 22, 2005

I have a table with the following 4 fields (these are the ones i'm having the issue with but there are others).

ID, Country, City, Date

I enter data into this table via a form

The ID is an auto number. The date is simply enetered.

The Country Field is a lookup (in the table itself) with the following lookup properties

Display Control: Combo Box

Row Source Type: Value List

Row Source: "England";"Spain";"France" etc


Now the problem...

I also want a lookup in the City combobox (on the form) which changes to reflect what was selected in the Country field.

E.G

If in the Country Field Spain is selected then in the City Field I should have the option of selecting Barcelona, Madrid, Valencia etc.

I was told to use the following code in the afterupadate of "parent combobox":

Private Sub ComboCountry_AfterUpdate()
Select Case Me.ComboCity
Case "Test"
Me.ComboCity.RowSource = "A;B;C"
End Select
End Sub

Ive tried it but as always i get an error when i use the country drop down...

A pop up box with:

Compile error:
Method or data member not found

and visual basic opens with the top line of the code "Private Sub Country_AfterUpdate()" highlighted in yellow, and the ".RowSource =" is highlighted in blue.

These are the properties of my form

My Country Combobox is called - "Country"
My City Combobox is called - "City"

I have put the above code in the after update of the "Country" combobox as below:

Private Sub Country_AfterUpdate()
Select Case Me.City
Case "England"
Me.City.RowSource = "London;Manchester;Leeds"
End Select
End Sub


Anyone know where the error in the code is? Obviously i have reduced the code to include only one case.

Attached is the DB

View 3 Replies View Related

Simple Query Question: Setting A Field To A Set Value

Jul 6, 2007

Putting this in the field box in a query:

read or write: "r"

should create a field called read or write with a value of r in each record in the query, shouldn't it?

Why doesn't it, and how can I make it work?

View 2 Replies View Related

Currency Field Formatting - Simple But Annoying Issue!

Jan 10, 2007

Hi All,

This is my first post so can I just say how great and useful this forum has been for me since I joined! I have found pretty much everything I have wanted without having to ask, so thank you everyone!

I am only beginning with Access at the moment, as I try to implement some improvements to the way my company works!

I have one really simple, but annoying issue at the moment and I cannot find the answer anywhere!

I have a currency filed in my table. I want to format it in USD, but USD is not available in the format options, just Ł and €. I cannot see where the hell I get the USD $ format from?

I know this is probably well simple and I am being a plum!

View 3 Replies View Related

General :: Simple Text Field Comparison To Blanks

Jul 16, 2012

I'm using Access and VB for the first time, trying to develop an application for hobby use. I built and populated my database, and I'm building my first module. Unfortunately, one of the most simple actions is tripping me up. I'm attempting to perform an action only if a 255-character text field in a file doesn't contain data, but comparing it to " " or "Space$(n)" doesn't yield the actions I'm expecting.

View 2 Replies View Related

Queries :: Setting To Only Show Field In Simple Query Once

Sep 19, 2013

I have a simple query between two tables joined together by common fields. In my first table (Table 1 - tblLocations) I have information about a building i.e. Location Code, address and total sqft. . In my second table (Table 2 - tblAllocatedSpace), this contains details (Location Code, Room ID, SqFt assigned, etc.) of the space allocated in each building. The two tables are joined together when the “Location Code” in both table match.

In my query, I show the location detail from (Table 1 - tblLocations) and related records from (Table 2 - tblAllocatedSpace). My result looks like the following:

Location Code Sqft Address Assigned Sqft
106067 1,000 600 March Rd 10
106067 1,000 600 March Rd 15
106067 1,000 600 March Rd 12
106067 1,000 600 March Rd 20

The location code, Address and Sqft is rebated each time a space is assigned in (Table 2 – tblAllocatedSpace). When a build a report and need to sum the location Sqft, the number is multiplied by the number of related records in (Table 2 – tblAllocatedSpace). In this example by building total space is 4,000 sqft when I only it to show 1,000.

How do I set to only show the location code and sqft once?

View 1 Replies View Related

Adding To Value In Field

May 21, 2005

Ok, this may be a ridiculous question to ask, and i imagine there is a simple answer, but i cant see it. maybe its coz i learnt access from the "for dummies" guide :)

i have a table with a "quantity" field. i have a form for finding a record, and then i want to enter a value in a box and press a button to add it to the existing value in the "quantity" field. basically it is for increasing the amount of a certain type of item in stock when a new delivery comes in.

Please help me, im a simple man and havent got a clue

thanks

View 2 Replies View Related

Adding Same Field Twice

Mar 7, 2013

I got a question about a database I'm trying to design. It's a small cable management system.

I want to create a form that shows where the port of a computer is connected to.

For example; A computer has one ethernet port. That port is connected to a switch. I can add the fields for the Unique hardware ID and for the ports of the computer. But I can't seem to chose a device where the computer is connected to.

Ive tried working with queries.. but nothing seems to work so far.

I created a small example of the database I'm trying to make.

example_database.zip

View 7 Replies View Related

How Would I Go About Adding A Field Or A Table?

Oct 2, 2005

The attachment is the database Im working on and I have a problem.
I need to add a supplier Product number in there some where so I could keep the numbe as reference and so it may be easier to enter items recieved.
in the form section, I will have Invoice with a sub form Invoice details.
I need to put suppliers Item number somehow so it would search and if the item is on the product list it would fill out the rest of the values.

should I make a separate table for suppliers product number or should I just include that into the invoice detail? I dont want to put it in the product table since I get same Items from different suppliers.

View 1 Replies View Related

Adding To Access From The Field

Nov 26, 2007

Hi again. I finally have time to start working on my access project, and what I want to know is how could a field technician add to access from the field, either with a laptop or possible even a pda. things a tech would need to add are notes, different readings that monitor job progress, equipment placed at the job site, other things like that.

View 3 Replies View Related

Adding A Field Name To A Query

Nov 30, 2005

I hope I can ask this question so that it makes sense.

I have a totals query that I'm using to make a Chart. The query works fine but I want to change the look abit. What I'm doing is quering a table looking for the total number of times that there is a check box in a field called PDC RTA and totaling the Hrs as well. The data comes back looking looking like this.
PDC RTA-----Count-----Total Hrs
-1 ---------- 6 -------- 53
0 -----------25 -------- 78

My problem is that when I chart this the -1 and 0 show up at the bottom of the bars on the chart. I would like to rename these to fields on the qry qrid then I can chart the new field names so that it will make sense to the user.

Make sense

Jon

View 2 Replies View Related

Adding A String To The End Of A Field

Nov 20, 2007

I have a field that contains various text....(It's a note field that allows 86 characters).

I want to go in to the database and add the string "Conversion 2007" to the end of every record. So leave whatever is in there but add in the string. I would like it to add whatever it will fit, in the cases where there is not enough room.

I figured this was somewhat simple, but I haven't found anything yet.
Thanks.

View 4 Replies View Related

Adding Field To A Form

Jul 13, 2005

I am using a Microsoft Access Form and would like to add some new fields and have no idea where to start. Any step-by-step help would be greatly appreciated.

I am an amateur and would need step by step so minute details would be greatly appreciated.

Thanks!

View 2 Replies View Related

Form Adding In A Field

Dec 1, 2006

Hi

Can somebody help me with forms. I have not used Acecess for sometime and have created a form and realised that I had not included another field in the form. Can somebody help me I have forgotten how to add in the additional field. Simply please:eek:

View 1 Replies View Related

Form Adding In A Field

Dec 1, 2006

Hi

Can somebody help me with forms. I have not used Acecess for sometime and have created a form and realised that I had not included another field in the form. Can somebody help me I have forgotten how to add in the additional field. Simply please:eek:

View 3 Replies View Related

Adding A New Field Into A Form

Dec 4, 2006

Hello

I have created a form and now realised that I have forgotten to put in a field. I have forgotten how to add an additional field to the form. Help in plain terms please. Thanks:eek:

View 2 Replies View Related

Adding A Value To New Field In Access

Jun 5, 2012

I'm importing a text file into Access. The file is just a string of numbers that I'm breaking apart into three fields. Once those three fields have been broken apart by the import tool I need to add a new field that just contains the year.

For example, I import the 1990 data file that is .txt format and I break apart the three fields while it's imported. Once imported into three fields, I want to add a 4th field that says 1990 for every record. This has to be a pretty simple thing to do, but I can't figure out how to add a single number as a new field to every record in the database.

View 2 Replies View Related

Adding Value To A Field When Condition Is Met

May 19, 2015

I need for a valued to be changed in a row after it checks for how many rows have another value, counting how many and that number making it the amount field.

Example:
Fields- Name sponsor amount

I want access to show how many people have been sponsored by Gabriel... So if 3 where sponsored by Gabriel show 3 to the amount field for Gabriel row! Any way to do this or an easier way?

I for it to check it every time a user is added incase the user added is sponsored by Gabriel add it automatically!

View 14 Replies View Related

Adding A New Field To A Report?

Aug 6, 2013

I'd like to add a customer type field from my 'Customer:Table' to my 'Customer Balance Report' Also, I need to create a validation rule in my table specific the legal values of SAL, SPG and WMN in my customer type field. with validation text, which I'm not sure how to do.

View 2 Replies View Related

Adding A Field To A Table In Access

Aug 4, 2006

I have created the code below to add a new column to a table each month. This may not be the best database design but it meets our needs for now.

However I am having difficultly with the code below. The CreateField Function is unable to accept the parameter periodDate. Any Suggestions on this would be apprerciated




Function DateField() As Long

Dim colFullName As Object
Dim dbsCurrent As Object
Dim yearInt As Integer
Dim monthInt As Integer
Dim table1 As Object


Set dbsCurrent = CurrentDb
Set table1 = CurrentDb.CreateTableDef("103TblCustomerBalancesCombined")

yearInt = Year(Date)
monthInt = Month(Date) - 1

If monthInt = 0 Then
periodDate = CLng(yearInt - 1 & 12)
Exit Function
End If

If monthInt < 10 Then
periodDate = CLng(yearInt & "0" & monthInt)
Else
periodDate = CLng(yearInt & "" & monthInt)
End If

Set colFullName = table1.CreateField(periodDate, DB_TEXT)
table1.Fields.Append colFullName


End Function

View 2 Replies View Related

How To Update Access After Adding A New Field?

Apr 27, 2007

Hi

1. I need to know how to update the field list of the query after
adding a new field into the table and the query?
2. I need to know how to update the records table after adding
calculated filed?

View 1 Replies View Related

Adding An Autonumber Field Automatically

Apr 27, 2007

Hi,

Can somebody help...
I'm trying to put via an automatic way a field to a table that autonumbers. When I use a select-query: ALTER TABLE tablename ADD COLUMN Id Autonumber, Access doesn't recognizes the type "Autonumber". Even in VBA when I use the .createfield method, I can't set the datatype "Autonumber". I'm pretty sure it must be possible (as access can store anyway the indexes.. actually, I want those indexes as numbers in a separate field).

Thanks a million for any help!!!
Leen

View 2 Replies View Related







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